First matching row - 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)

First matching row

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-03-12 : 15:37:43

I have a table with:

AppName
ExeName

Sometimes, I have duplicate AppNames with different filenames. This was before I was here and I can't correct it now. I need a SQL Select statement that will take the FIRST occurrence off AppName and its corresponding ExeName only. The order in which it occurs in the database is fine. It really doesn't matter at this point. I just can't deal with two or more results. Later, they will want to correct the data, but I just need to get it working. Any ideas? They are both varchar fields.

Duane

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2010-03-12 : 15:58:46

[code]
DECLARE @tab TABLE(
appname VARCHAR(100),
exename VARCHAR(100)
)

INSERT INTO @tab
(appname,
exename)
SELECT 'App1',
'App1Exe1'
UNION ALL
SELECT 'App1',
'App1Exe2'
UNION ALL
SELECT 'App2',
'App2Exe1'
UNION ALL
SELECT 'App3',
'App3Exe1'
UNION ALL
SELECT 'App3',
'App3Exe2'

SELECT appname,
exename
FROM (SELECT appname,
exename,
Row_number()
OVER(PARTITION BY appname ORDER BY appname) AS num
FROM @tab) t
WHERE num = 1
[/code]

Ehan
Starting Member

19 Posts

Posted - 2010-03-12 : 16:00:50

select AppName,max(ExeName) from table
group by AppName

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-13 : 02:19:00

quote:
Originally posted by Ehan

select AppName,max(ExeName) from table
group by AppName

you cant guarantee that ExeName returned will be that associated to FIRST occurrence record by this method

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-03-25 : 16:44:39

Thank you for all these tips. I must have been distracted that day. I didn't even see all this. In Ehan's case, if the simple query runs (I thought MAX() only worked with numerical data), that it would be all I need because I don't care which result comes up. I don't fully understand the UNION query above, but I am going to take a closer look and maybe I can use some of what I learn for other things.

Duane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources