CODEWITHSUNDEEP
sqlsql-serverdatabase
sdespont
sdespont

Reputation: 14025

SQL use distinct on only one specific field

How can I select only one line by shortDesc field? I don't care about which line, but only I need only one line.

My table extract would be

|longDescr|shortDesc|
|---------|---------|
| First 1 | first   |
| First 2 | first   |
| First 3 | first   |
| First 4 | first   |
| Second 1| second  |
| Second 2| second  |
| Second 3| second  |
| Second 4| second  |
| Third 1 | third   |
| Third 2 | third   |
| Third 3 | third   |

And what I am expected is as cursor result:

|longDescr|shortDesc|
|---------|---------|
| First 1 | first   |
| Second 1| second  |
| Third 1 | third   |

I really don't know how I could write my SQL query. Any ideas?

Upvotes: 0

Views: 104

Answers (2)

Himanshu
Himanshu

Reputation: 32602

Try this one using GROUP BY clause:

SELECT MIN(longDescr) AS longDescr, shortDesc
FROM MyTable
GROUP BY shortDesc

Output:

╔═══════════╦═══════════╗
║ LONGDESCR ║ SHORTDESC ║
╠═══════════╬═══════════╣
║ First 1   ║ first     ║
║ Second 1  ║ second    ║
║ Third 1   ║ third     ║
╚═══════════╩═══════════╝

See this SQLFiddle

You can use MIN() or MAX() function as per your requirement.

Upvotes: 1

Amit Singh
Amit Singh

Reputation: 8109

Select longDescr,shortDesc from
(Select Row_Number() Over(Partition By shortDesc order by shortDesc) as Rows ,*
 from TableName ) t 
where Rows=1;

Upvotes: 2

Related Questions