first occurance of record
I have a able with three columns
REP,ZIP,DATE
I need to select the first record of every unique ZIP there is. In other word.
Select every record that has a unique zip code(the first one it finds)
hope that makes sense.
REP,ZIP,DATE
I need to select the first record of every unique ZIP there is. In other word.
Select every record that has a unique zip code(the first one it finds)
hope that makes sense.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select REP,ZIP,DATE from
(
select *, row_number() over (partition by zip order by date desc) rowno
from yourtable
) a
where a.rowno = 1
(
select *, row_number() over (partition by zip order by date desc) rowno
from yourtable
) a
where a.rowno = 1
The above query should return latest date's zip records
If you want oldest date's zip records, try this query
select REP,ZIP,DATE from
(
select *, row_number() over (partition by zip order by date) rowno
from yourtable
) a
where a.rowno = 1
Note tested, since from mobile
Raj
If you want oldest date's zip records, try this query
select REP,ZIP,DATE from
(
select *, row_number() over (partition by zip order by date) rowno
from yourtable
) a
where a.rowno = 1
Note tested, since from mobile
Raj
If you just want unique Zips, @ewangoya looks close. If you want the first row for each unique Zip that is a different matter.
Unique Zips
SELECT DISTINCT Zip FROM Table
If you want the entire row data, please answer this question: When you say you want to select the "First" record, what do you mean, are they ordered somehow? Perhaps by date?
Unique Zips
SELECT DISTINCT Zip FROM Table
If you want the entire row data, please answer this question: When you say you want to select the "First" record, what do you mean, are they ordered somehow? Perhaps by date?
ASKER
perfect. thanks
@acardullo: Did you test the accepted query properly !? Is that result you asked for ?
The accepted solution, fetch the MAX of each column based on ZIP. Not from same row, each of the columns could be different rows of the same ZIP.
To be more clear see the screenshot below - First recordset is the samle data (4 rows). Next one is the result from the accepted solution's query
(Click on screenshots to open)
This is the result, based on my query
This is test data that I checked. Copy Paste to SQL Server Management Studio and check
Hope this make sense!
Raj
The accepted solution, fetch the MAX of each column based on ZIP. Not from same row, each of the columns could be different rows of the same ZIP.
To be more clear see the screenshot below - First recordset is the samle data (4 rows). Next one is the result from the accepted solution's query
(Click on screenshots to open)
This is the result, based on my query
This is test data that I checked. Copy Paste to SQL Server Management Studio and check
create table #table
(
ZIP VARCHAR(10),
REP VARCHAR(10),
DATE DATETIME
)
INSERT INTO #table
SELECT '12345', 'AAAA', '03/15/2011' UNION ALL
SELECT '12345', 'ZZZZ', '03/01/2011' UNION ALL
SELECT '12346', 'AAAA', '03/12/2011' UNION ALL
SELECT '12346', 'ZZZZ', '03/01/2011'
SELECT * FROM #TABLE
-- Accepted Solution
SELECT * FROM #TABLE
select MAX(ZIP) ZIP, MAX(REP) REP, MAX(DATE) DATE
from #table
group by ZIP
order by DATE DESC
-- My Query
SELECT * FROM #TABLE
select REP,ZIP,DATE from
(
select *, row_number() over (partition by zip order by date DESC) rowno
from #table
) a
where a.rowno = 1
Drop Table #TABLEHope this make sense!
Raj
select distinct zip
from table
order by zip