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!

@raina77ow, my mistake, the suggestion popped up MySQL and I must have misread it and clicked on it. GROUP BY is giving me errors. I'm going to investigate it a bit further to see where the issue is. Likely it's poorly formatted somewhere on my part. — LuFaMa, Oct 07 '13 at 16:03
@Moob, it doesn't matter. Any row will do, I just need a sampling. — LuFaMa, Oct 07 '13 at 16:04

score 1 · Accepted Answer · answered Oct 07 '13 at 15:58

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.

score 0 · Answer 2 · edited Oct 07 '13 at 19:10

0

WITH RankedQuery AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY TypeID ORDER BY [ordercolumn] DESC) AS rn
   FROM         [table]
)
SELECT *
FROM RankedQuery
WHERE rn = 1;

This will return the top row for each type id, you can add an order by if you want a specific row, not just any.

edited Oct 07 '13 at 19:10

answered Oct 07 '13 at 15:59

user1948635

1,357
4
15
22

Select unique records

2 Answers2