Awkward Query on Two Key Fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Awkward Query on Two Key Fields

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 Name
73 45879 Jan 01, 2014 Brian
74 45880 Jan 01, 2014 Carol
75 45881 Jan 02, 2014 Julian
75 45882 Jan 02, 2014 Julian
76 45883 Jan 03, 2014 Tim
77 45884 Jan 04, 2014 Terry
78 45885 Jan 05, 2014 Jonah
78 45886 Jan 05, 2014 Jonah
78 45887 Jan 05, 2014 Jonah
79 45888 Jan 06, 2014 Susan
80 45889 Jan 07, 2014 Bill
81 45890 Jan 08, 2014 Michelle
81 45891 Jan 08, 2014 Michelle
81 45892 Jan 08, 2014 Michelle
81 45893 Jan 08, 2014 Michelle
81 45894 Jan 08, 2014 Michelle
82 45895 Jan 08, 2014 Jimmy

I would like a query that would pull the non-red information only:

Account_ID Account_Sequence Open_Date Name
73 45879 Jan 01, 2014 Brian
74 45880 Jan 01, 2014 Carol
75 45881 Jan 02, 2014 Julian
75 45882 Jan 02, 2014 Julian
76 45883 Jan 03, 2014 Tim
77 45884 Jan 04, 2014 Terry
78 45885 Jan 05, 2014 Jonah
78 45886 Jan 05, 2014 Jonah
78 45887 Jan 05, 2014 Jonah
79 45888 Jan 06, 2014 Susan
80 45889 Jan 07, 2014 Bill
81 45890 Jan 08, 2014 Michelle
81 45891 Jan 08, 2014 Michelle
81 45892 Jan 08, 2014 Michelle
81 45893 Jan 08, 2014 Michelle
81 45894 Jan 08, 2014 Michelle
82 45895 Jan 08, 2014 Jimmy

Hopefully 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 :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources