Author |
Topic |
arowberry
Starting Member
2 Posts |
Posted - 2014-04-09 : 12:09:49
|
Hi All,New here and reasonably new to SQL, although I manipulate data and produce reports as a profession.I have some tables that have two key fields, Account_ID and Account_Sequence, however the table setup isn't great and as a result I have little idea of how to pull the data I require, though I can explain what I am trying to do.Account_Sequence is completely unique, some Account_ID's have multiple Account_Sequence numbers. I need to pull all information from the table, so that I pull each Account_ID and the minimum Account_Sequence number.This is a mock sample of the data contained in table called 'Accounts':Account_ID Account_Sequence Open_Date Name73 45879 Jan 01, 2014 Brian74 45880 Jan 01, 2014 Carol75 45881 Jan 02, 2014 Julian75 45882 Jan 02, 2014 Julian76 45883 Jan 03, 2014 Tim77 45884 Jan 04, 2014 Terry78 45885 Jan 05, 2014 Jonah78 45886 Jan 05, 2014 Jonah78 45887 Jan 05, 2014 Jonah79 45888 Jan 06, 2014 Susan80 45889 Jan 07, 2014 Bill81 45890 Jan 08, 2014 Michelle81 45891 Jan 08, 2014 Michelle81 45892 Jan 08, 2014 Michelle81 45893 Jan 08, 2014 Michelle81 45894 Jan 08, 2014 Michelle82 45895 Jan 08, 2014 JimmyI would like a query that would pull the non-red information only:Account_ID Account_Sequence Open_Date Name73 45879 Jan 01, 2014 Brian74 45880 Jan 01, 2014 Carol75 45881 Jan 02, 2014 Julian75 45882 Jan 02, 2014 Julian76 45883 Jan 03, 2014 Tim77 45884 Jan 04, 2014 Terry78 45885 Jan 05, 2014 Jonah78 45886 Jan 05, 2014 Jonah78 45887 Jan 05, 2014 Jonah79 45888 Jan 06, 2014 Susan80 45889 Jan 07, 2014 Bill81 45890 Jan 08, 2014 Michelle81 45891 Jan 08, 2014 Michelle81 45892 Jan 08, 2014 Michelle81 45893 Jan 08, 2014 Michelle81 45894 Jan 08, 2014 Michelle82 45895 Jan 08, 2014 JimmyHopefully that makes sense. Please let me know if I haven't followed all of the forum rules correctly, or if any further information would help.I hope someone can help me :-) |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-04-09 : 13:17:36
|
You can use the row_number function like in the example below:SELECT Account_ID, Account_Sequence, Open_Date, [Name]FROM( SELECT *, ROW_NUMBER() OVER (PARTITION BY Account_ID ORDER BY Account_Sequence) AS RN FROM YourTable)s WHERE RN=1 |
|
|
arowberry
Starting Member
2 Posts |
Posted - 2014-04-09 : 14:18:20
|
Thank you!This works perfectly, though I won't pretend that I fully understand it at the moment :-) |
|
|
|
|
|