Want tall the first rows only matching a criteria

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 2000 Forums

SQL Server Development (2000)

Want tall the first rows only matching a criteria

Author

Topic

puneet_pal
Starting Member

3 Posts

Posted - 2004-08-08 : 07:51:43

Hi all,
I need all the first rows only matching a criteria.
Like I have the following table:
EMP
--------------------
empname empsal
--------------------
a 1
a 2
b 2
b 4
c 1
c 2

Now I need all the first salaries mentioned for a,b and c all in the same order. So the result should be like this

--------------------
empname empsal
--------------------
a 1
b 2
c 1

If anyone could help me then it would be gr8.
Thanks,
Puneet
puneet_pal@yahoo.com

gpl
Posting Yak Master

195 Posts

Posted - 2004-08-08 : 07:55:46

What do you mean by first ? the lowest ? the highest by date ?

you want to do something like


select empname, min(empsal)
from emp
group by empname
order by empname

Graham

puneet_pal
Starting Member

3 Posts

Posted - 2004-08-08 : 09:11:36

Actually that is the problem I dont want to sort it on any min or max sal or any other criteria. I want in the same order that it is lying in the DB table.
Like for a column "name" I want the first occurance of the sal in the table and same for column "name" with value b and c.
Which means the query should take only the first occurance of the record and ignore all others after that with the respective column value. I think I am clear now, if still confused then let me know

Thanks
Puneet

gpl
Posting Yak Master

195 Posts

Posted - 2004-08-08 : 09:50:33

Puneet
There isnt any order in the database (ok, so physically there is, but you cant rely on it)

I suggest you add an ordering column and use that to detect the value you want to pull back.

Graham

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-08-08 : 20:24:34

possibly:

select * into newtable from EMP where 1 = 2
create unique index u1_idx on newtable (empname)
insert newtable
select * from EMP
select * from newtable

--Ken
"Knowledge is a process of piling up facts; wisdom lies in their simplification."

lysalex
Starting Member

1 Post

Posted - 2004-08-09 : 02:27:58

Hi, I think I am facing the same problem as puneet here. I think what puneet wants is something like this:

oo ou ov
----------
a1 a2 a3
a1 a2 a4
a1 a2 a5
b1 b2 b3
b1 b2 b4
b1 b2 b5

Assume that the top 3 and bottom 3 records are actually the same records respectively, with ov being the only difference that is causing the duplication.

What puneet is asking if there is a way to select only the first rows of the record set (a1 a2 a3) and (b1 b2 b3) without modifying the value in the other records

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-08-09 : 13:37:51

What I suggested will do that.

--Ken
"Knowledge is a process of piling up facts; wisdom lies in their simplification."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources