Microsoft SQL Server Query is Returning Wrong Result

Question

I have the following code:

$GetUID = $c->query("SELECT UserUID FROM dbo.Users_Master ORDER BY UserID DESC");
$UserUniqueID = $GetUID->fetch(PDO::FETCH_ASSOC);
$UUID = $UserUniqueID['UserUID'];
$NewUUID = $UUID + 1;

This is successfully working, but the problem is something with my query.

It is always entering 12 into my database.

I went to the Microsoft SQL query window and entered:

USE UserData;
SELECT UserUID FROM dbo.Users_Master ORDER BY UserID DESC

and it returned:

11, 17, 15, 19, 16, 18, 10, 13, 12, 14, 3

Which is not exactly ordering by the highest to lowest.

I then went on using:

USE UserData;
SELECT UserUID FROM dbo.Users_Master ORDER BY UserID ASC

Which returned:

3, 14, 12, 13, 10, 18, 16, 19, 15, 17, 11

I want to obtain the higest UserUID and +1 to it.. WHy is my code selecting 11 all the time?

You are selecting UserUID and set order to UserID ? UserID = UserUID? — bummi
– bummi, Commented Feb 4, 2013 at 22:18

Taryn · Accepted Answer · 2013-02-05 00:04:02Z

4

Sounds like UserId is not of the datatype int, you can cast() the value to order:

SELECT UserUID 
FROM dbo.Users_Master 
ORDER BY cast(UserID as int) DESC

See SQL Fiddle with Demo

However, the best solution is to alter the datatype in the table to match what is being stored.

To alter the table in SQL Server, you would use:

alter table dbo.Users_Master alter column UserID int;

This would store the data in the correct datatype so you would not have to cast() the value to order it.

Edit #1, based on your comment that UserId is a varchar it seems like you mean to ORDER BY UserUID DESC which is an int

edited Feb 5, 2013 at 0:04

answered Feb 4, 2013 at 22:16

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user1968541 Over a year ago

Msg 245, Level 16, State 1, Line 2 Conversion failed when converting the varchar value 'BravoSlayer' to data type int.

Taryn Over a year ago

@user1968541 is BravoSlayer the same field as UserID?

user1968541 Over a year ago

I have two columns, UserUID which is INT then UserID which is varchar

Taryn Over a year ago

@user1968541 in your OP you were requesting ORDER BY UserID DESC which if that is a varchar then you cannot cast that as an int. It sounds like you want to use ORDER BY UserUID DESC

user1968541 Over a year ago

That was the problem then! sorry for wastin' your time

|

Collectives™ on Stack Overflow

Microsoft SQL Server Query is Returning Wrong Result

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related