SQL select first records of rows for specific column

Question

I realize my title probably doesnt explain my situation very well, but I honestly have no idea how to word this.

I am using SQL to access a DB2 database.

Using my screenshot image 1 below as a reference:

column 1 has three instances of "U11124", with three different descriptions (column 2)

I would like this query to return the first instance of "U11124" and its description, but then also unique records for the other rows. image 2 shows my desired result.

image 1

image 2

----- EDIT ----

to answer some of the questions / posts: technically, it does not need to be the first , just any single one of those records. the problem is that we have three descriptions, and only one needs to be shown, i am now told it does not matter which one.

Solution 1

SELECT STVNST, MAX(STDESC) FROM MY_TABLE GROUP BY STVNST;

Solution 2

In SQL Server:

select stvnst, stdesc
from (
  select     
     stvnst, stdesc
     row_number() over (order by stdesc partition by stvnst) row
  from table
) a
where row = 1

This method has an advantage over a simple group by, in that it will also work when there's more than two columns in the table.

Solution 3

SELECT STVNST,FIRST(STDESC) from table group by STVNST ORDER BY what_you_want_first

Solution 4

All you need to do is use GROUP BY.

You say you want the first instance of the STDESC column? Well you can't guarntee the order of the rows without another column, however if you want to order by the highest ordered value the following will suffice:

SELECT STVNST, MAX(STDESC) FROM MY_TABLE GROUP BY STVNST;

View more solutions

Comments

[Byron Whitlock]

MAX won't get the first value, just the highest ordered value.

[Blorgbeard]

Yeah, you probably want MIN actually.

[p.campbell]

+1. Could be MAX or MIN if the desire is alphabetical when defining OP's requirement for "first".

[Adam]

Sorry I did not clarify (until just now) . . I am using SQL to access our DB2 database. I just tried this FIRST(stdesc) and I am gettin an error message that the FIRST command doesnt exist.. =[

[p.campbell]

In which RDBMS is FIRST supported?

[Lynette Duffy]

+1 Yes it will, depending on the version. ref this post: stackoverflow.com/questions/4879832/conditional-db2-sql-quer‌​y/…

[Nick]

In your post, you have a comma after "row" in your sub-select. This caused an error in my query. Removing the comma fixed the issue.