select query - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

select query

Author

Topic

mshsilver
Posting Yak Master

112 Posts

Posted - 2011-05-20 : 07:16:36

Hi,
I am running a query that retrieves a few thousand rows of data and in that data there will be multiple rows with the same company name and the same Section Code.

Now in the query I am using below I need a way of only showing only one row per company and section code. See the Current Example Results below, I only used three companies here but there will be many. Under that you will see the desired results.

My question is. Is there anything i can add to my query to filter that down?

Thanks for looking.


SELECT company, POSTALCODE, last_hs, next_hs, risk_level, sct1, sct2 from wce_contact c inner join wce_course_delegate_link d ON c.edrs = d.edrs_no where ((record_type='company'))

Current Example Results:
Company Postcode LastHS NextHS RiskLevel Section Code Section Title
A M Nursing DE14 2BG 21/01/2011 21/01/2012 Low B15 B15 Health & Social Care
A M Nursing DE14 2BG 21/01/2011 21/01/2012 Low B15 B15 Health & Social Care
A M Nursing DE14 2BG 21/01/2011 21/01/2012 Low B15 B15 Health & Social Care
A M Nursing DE14 2BG 21/01/2011 21/01/2012 Low B15 B15 Health & Social Care
A Norris LE67 3HF 1/2/2011 2/8/2011 Medium C218 C218 Commercial - Alternative Technologies
A Norris LE67 3HF 1/2/2011 2/8/2011 Medium C218 C218 Commercial - Alternative Technologies
A Norris LE67 3HF 1/2/2011 2/8/2011 Medium C218 C218 Commercial - Alternative Technologies
A Norris LE67 3HF 1/2/2011 2/8/2011 Medium C218 C218 Commercial - Alternative Technologies
A Norris LE67 3HF 1/2/2011 2/8/2011 Medium C218 C218 Commercial - Alternative Technologies
A Norris LE67 3HF 1/2/2011 2/8/2011 Medium C218 C218 Commercial - Alternative Technologies
A Norris LE67 3HF 1/2/2011 2/8/2011 Medium C218 C218 Commercial - Alternative Technologies
A Norris LE67 3HF 1/2/2011 2/8/2011 Medium C218 C218 Commercial - Alternative Technologies
A Norris LE67 3HF 1/2/2011 2/8/2011 Medium C218 C218 Commercial - Alternative Technologies
A N Automation DE7 4RJ 10/3/2011 9/3/2012 Low C14 C14 Motor Vehicle & Engineering
A P Parking DE74 2SA 9/12/2010 9/6/2011 Medium C10 C10 Creative Industries & Business
A P Parking DE74 2SA 9/12/2010 9/6/2011 Medium C10 C10 Creative Industries & Business
A P Parking DE74 2SA 9/12/2010 9/6/2011 Medium C10 C10 Creative Industries & Business
A P Parking DE74 2SA 9/12/2010 9/6/2011 Medium C10 C10 Creative Industries & Business
A P Parking DE74 2SA 9/12/2010 9/6/2011 Medium C10 C10 Creative Industries & Business

Desired results:
Company Postcode LastHS NextHS RiskLevel Section Code Section Title
A M Nursing DE14 2BG 21/01/2011 21/01/2012 Low B15 B15 Health & Social Care
A M Nursing DE14 2BG 21/01/2011 21/01/2012 Low B15 B15 Health & Social Care
A Norris LE67 3HF 1/2/2011 2/8/2011 Medium C218 C218 Commercial - Alternative Technologies
A N Automation DE7 4RJ 10/3/2011 9/3/2012 Low C14 C14 Motor Vehicle & Engineering
A P Parking DE74 2SA 9/12/2010 9/6/2011 Medium C10 C10 Creative Industries & Business

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-20 : 07:40:41

select *
from
(
select *, seq = rownumber over (partition by company, sectioncode order by .....)
) a
where seq = 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources