Select unique records

Question

I'm working with a table that has about 50 colums and 100,000 rows.

One column, call it TypeID, has 10 possible values:

1 thourgh 10.

There can be 10,000 records of TypeID = 1, and 10,000 records of TypeID = 2 and so one.

I want to run a SELECT statement that will return 1 record of each distinct TypeID.

So something like

TypeID    JobID    Language    BillingDt    etc
------------------------------------------------
1         123      EN          20130103     etc
2         541      FR          20120228     etc
3         133      FR          20110916     etc
4         532      SP          20130822     etc
5         980      EN          20120714     etc
6         189      EN          20131009     etc
7         980      SP          20131227     etc
8         855      EN          20111228     etc
9         035      JP          20130615     etc
10        103      EN          20100218     etc

I've tried:

SELECT DISTINCT TypeID, JobID, Language, BillingDt, etc

But that produces multiple TypeID rows of the same value. I get a whole bunch of '4', '10', and so on.

This is an ORACLE Database that I'm working with.

Any advise would be greatly appreciated; thanks!

Answer 1

You can use ROW_NUMBER() to get the top n per group:

SELECT  TypeID, 
        JobID, 
        Language, 
        BillingDt, 
        etc
FROM    (   SELECT  TypeID, 
                    JobID, 
                    Language, 
                    BillingDt, 
                    etc,
                    ROW_NUMBER() OVER(PARTITION BY TypeID ORDER BY JobID) RowNumber
            FROM    T
        ) T
WHERE   RowNumber = 1;

SQL Fidle

You may need to change the ORDER BY clause to fit your requirements, as you've not said how to pick one row per TypeID I had to guess.