select distinct two columns with random id

I would like to get distinct record of each Design and type with random id of each record It is not possible to use

select distinct Design, Type, ID from table

It will return all values This is structure of my table

Design | Type | ID
old chair 1
old table 2
old chair 3
new chair 4
new table 5
new table 6
newest chair 7

Possible result

Design | Type | ID
old table 2
old chair 3
new chair 4
new table 6
newest chair 7

Answers


If it doesn't matter which one, you can always take the maximum\minimum one:

SELECT design,type,max(ID)
FROM YourTable
GROUP BY design,type

This won't be randomly, it will always take the maximum\minimum one but it doesn't seems like it matters.


Try this one:

select *
from (
select *, row_number() over (partition by Design,Type order by id desc) rowID
from @tab
) x
where rowID = 1

Hope this one helps you :

WITH CTE AS
(
    SELECT Design, Type, ID, ROW_NUMBER() OVER (PARTITION BY Design, 
                                                   Type ORDER BY id DESC) rid
    FROM table
)
SELECT Design, Type, ID FROM CTE WHERE rid = 1 ORDER BY ID

Need Your Help

Can't get ng-cloak to work

angularjs ngcloak

I have a custom directive where I present the values in it using {{}} but the problem is when the page is reloading I get to see {{}} there before setting the values. I tried using ng-cloak but too...

Getting running total/percent from returned JSON

jquery ajax json

I'm running a query and getting JSON back with values representing the number of people in a specific group. My data comes back like this: