SQL To Sort 2 columns, one with dupes
-
Does anyone have a good solution given this table:
ID Name SortOrder
--------------------------------
1 Mike 3
1 Mike 7
2 Joe 2
3 Ed 1
4 John 4
4 John 0
Given that table I need to sort by sortOrder which is easy enough.
What I need is to be able to keep id's together in the sort or only
show one instance of the ID in the results..... Any ideas?
-
Select id, Name, Min(SortOrder) as MinSort from dbo.YourTable
group by id, name
order by MinSort
-
can you post an example of the output ?
* Noel -
if that is the answer you are excellent at guessing because I couln't understand it at all
* Noel -
Didn't mind trying a guess... took only 15 secs to write it
. -
Didn't work, that returns multiple copies of the records (More than are even in the DB)!
-
Told ya!
* Noel -
can you post an example of the output ?
* Noel -
Now unless you have a cross join, that's just not possible. Post the actual data, table definition and the expected output and you want usefull help.
-
The desired output is:
4 John 0
3 Ed 1
2 Joe 2
1 Mike 3
Ordered by "SortOrder", with only 1 value (any 1) of "Id" displayed.
-
The table design is:
int ID;
nvarchar Name;
float Sort;
Original Contents:
ID Name SortOrder
--------------------------------
1,Mike,3
1,Mike,7
2,Joe,2
3,Ed,1
4,John,4
4,John,0
The desired output is:
4 John 0
3 Ed 1
2 Joe 2
1 Mike 3
Ordered by "SortOrder", with only 1 value (any 1) of "Id" displayed.
-
Declare @A table (id int not null, Name varchar(15) not null, SortOrder tinyint not null)
Insert into @A (id, name, SortOrder) values (1, 'Mike', 3)
Insert into @A (id, name, SortOrder) values (1, 'Mike', 7)
Insert into @A (id, name, SortOrder) values (2, 'Joe', 2)
Insert into @A (id, name, SortOrder) values (3, 'Ed', 1)
Insert into @A (id, name, SortOrder) values (4, 'John', 4)
Insert into @A (id, name, SortOrder) values (4, 'John', 0)
Select id, Name, Min(SortOrder) as MinSort from @A
group by id, name
order by MinSort
id Name MinSort
----------- --------------- -------
4 John 0
3 Ed 1
2 Joe 2
1 Mike 3
Did you actually try my query?????????????????
-
In that case the query posted by Remi should work !!
* Noel -
Nice guess I guess
. -
I have to learn to refresh before I post so this does not happen again
* Noel
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply