Query to get active record - 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 2000 Forums

Transact-SQL (2000)

Query to get active record

Author

Topic

cornall
Posting Yak Master

148 Posts

Posted - 2008-07-17 : 05:12:30

Hi

I have a table


Active, Name
   1    Dave
   0    Dave
   0    Steve

I want a quey that returns the active record for a given name unless there is not an active record then I want the inactive one

so I want

1 Dave
0 Steve

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-17 : 05:23:10

One of these should do it...

select max(Active) as Active, Name from myTable group by Name

--or (if datatype of Active is bit)
select max(case when Active = 1 then 1 else 0 end) as Active, Name from myTable group by Name

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

cornall
Posting Yak Master

148 Posts

Posted - 2008-07-17 : 05:28:18

Thanks for the answer

I think I have oversimplified my question this is actualy part of a join so I only want it to join to the active record or (the inactive record if there is only 1 record)


INNER JOIN		myTable MT
				ON MT.Name = OtherTable.Name

So I need the AND!

cornall
Posting Yak Master

148 Posts

Posted - 2008-07-17 : 05:32:48

Your answer got me thinking and i am going to try


INNER JOIN		myTable MT
				ON MT.Name = OtherTable.Name 
                                AND MT.Active = (SELECT MAX(Active) FROM myTable MT2 WHERE ON MT2.Name = OtherTable.Name)

cornall
Posting Yak Master

148 Posts

Posted - 2008-07-17 : 05:38:05

That worked perfectly however thinking to the future. There could be a senario where a user has more than one inactive record.

e.g.


Active, Name
   1    Dave
   0    Dave
   0    Steve
   0    Steve

My code would return 2 rows for steve and I only realy want 1!

P.S. I did not write this system which joins on name rather than something sencible!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-17 : 05:42:50

quote:
Originally posted by cornall

That worked perfectly however thinking to the future. There could be a senario where a user has more than one inactive record.

e.g.

Active, Name
1 Dave
0 Dave
0 Steve
0 Steve

My code would return 2 rows for steve and I only realy want 1!

P.S. I did not write this system which joins on name rather than something sencible!

Can you post the full code you used?

Madhivanan

Failing to plan is Planning to fail

cornall
Posting Yak Master

148 Posts

Posted - 2008-07-17 : 05:53:25

Its cool I used Ryans sugestion of MAX against the staff number to get the latest record. Unfotunatly the second system doesnt hold staff numebr so I can only join on name!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources