Help with a select statement - 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)

Help with a select statement

Author

Topic

evidica
Starting Member

17 Posts

Posted - 2007-03-07 : 15:53:24

I have a select statement:


SELECT pba.ID, pba.Code, pba.Name, pbs.Scale As Gravity, pba.GroupID, pba.SortID
FROM PriceBulletinAreas pba LEFT JOIN PriceBulletinScales pbs ON pba.GroupID = pbs.GroupID WHERE
pba.GroupID=1 AND pbs.AreaID = 1 AND pbs.EffDate <= '3/7/2007' ORDER BY pba.SortID, pbs.EffDate DESC

It is returning duplicate ID's because PriceBulletinAreas has multiple entries for the ID which has a timestamp associated with it. How do I only select the most recent entries?

evidica
Starting Member

17 Posts

Posted - 2007-03-07 : 16:08:38

Another way to ask is how to do set it so that it does not return more than one pba.ID

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-07 : 16:29:20

You would use GROUP BY for this. You will probably need to use a derived table though to get the other columns not involved in the aggregates or GROUP BY. So please show us some sample data of your tables plus the expected result set.

Tara Kizer

evidica
Starting Member

17 Posts

Posted - 2007-03-07 : 16:41:26

PriceBulletinAreas:


[ID]    [Code]          [Name]                  [Gravity] [GroupID] [SortID]
1       KS_FORTY	Kansas Common	        B	  1	    1
2       NEB_B	        Intermediate	        B	  2	    4
7       KS_EASTERN	Eastern Kansas	        B	  1	    3
8       KS_SC	        South Central Kansas	B	  1	    2

PriceBulletinScales


[ScaleID]  [AreaID]  [EffDate]                  [Scale] [GroupID]
1          1	     1977-01-01 00:00:00.000	B	1	
2          2	     1977-01-01 00:00:00.000	B	2	
7          7	     1977-01-01 00:00:00.000	B	1	
8          8	     1977-01-01 00:00:00.000	B	1	
10         1	     2007-03-07 00:00:00.000	1	1	
11         2	     2007-03-07 00:00:00.000	1	2	
16         7	     2007-03-07 00:00:00.000	1	1	
17         8	     2007-03-07 00:00:00.000	1	1

My select returns:

 
1 KS_FORTY	Kansas Common	1	1	1	
1 KS_FORTY	Kansas Common	B	1	1	
8 KS_SC	South Central Kansas	1	1	2	
8 KS_SC	South Central Kansas	B	1	2	
7 KS_EASTERN	Eastern Kansas	1	1	3	
7 KS_EASTERN	Eastern Kansas	B	1	3

But I want it to return (Note the 4th column is 1's not B's):


1 KS_FORTY	Kansas Common	1	1	1
8 KS_SC	South Central Kansas	1	1	2
7 KS_EASTERN	Eastern Kansas	1	1	3

evidica
Starting Member

17 Posts

Posted - 2007-03-08 : 10:12:27

anyone? A little more insight would help even if you dont have the full solution.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-08 : 10:31:57

Add WHERE condition to exclude Scale = 'B'

SELECT pba.ID, pba.Code, pba.Name, pbs.Scale As Gravity, pba.GroupID, pba.SortID
FROM PriceBulletinAreas pba LEFT JOIN PriceBulletinScales pbs ON pba.GroupID = pbs.GroupID WHERE
pba.GroupID=1 AND pbs.AreaID = 1 AND pbs.EffDate <= '3/7/2007' and pbs.Scale <> 'B'
ORDER BY pba.SortID, pbs.EffDate DESC

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

evidica
Starting Member

17 Posts

Posted - 2007-03-08 : 10:39:15

but the issue here, is that if someone adds a row to add a new scale, I still need it to select only the correct ones, which would mean I would have to update my select statement any time there is a change.

Not the best at T-SQL, but I do know a thing or two.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-08 : 10:59:22

What is the criteria for "Correct Scales"? How would you decide whether to include "B" or 1?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rlaubert
Yak Posting Veteran

96 Posts

Posted - 2007-03-08 : 11:19:51

Your statement say Left Join... therefore ALL records in the PRiceBulletinAreas will be returned regardless of a match to the records in PriceBulletinScales. Since PriceBulletinScales has multiple matches it is returning multiple rows. Why can't you use an equal join if you only want rows that have entries with a match. If you truely want all rows from the PriceBulletinAreas then you need to filter/restrict based on PriceBulletinScales in the where clause.

Raymond Laubert
MCDBA, MCITP:Administration, MCT

rlaubert
Yak Posting Veteran

96 Posts

Posted - 2007-03-08 : 11:20:55

Try a having clause for max(effectivedate)

Raymond Laubert
MCDBA, MCITP:Administration, MCT

evidica
Starting Member

17 Posts

Posted - 2007-03-08 : 11:45:10

harsh_athalye: EffDate determines. whichever has the newest date is the one I want to use.

rlaubert: What will max(effDate) do for me?

Not the best at T-SQL, but I do know a thing or two.

evidica
Starting Member

17 Posts

Posted - 2007-03-08 : 13:53:42

impossible I guess, I cannot seem to figure it out.

Not the best at T-SQL, but I do know a thing or two.

evidica
Starting Member

17 Posts

Posted - 2007-03-08 : 16:36:03

Here is as far as I have gotten so far:


SELECT TOP (Select top 1 count(*) from PriceBulletinScales WHERE GroupID = 1 Group By EffDate ORDER BY EffDate DESC) 
pba.ID, pba.Code, pba.Name, pbs.Scale As Gravity, pba.GroupID, pba.SortID
FROM PriceBulletinAreas pba Left JOIN PriceBulletinScales pbs ON pba.GroupID = pbs.GroupID WHERE
pba.GroupID=1 AND pbs.AreaID = 1 AND pbs.EffDate <= '3/8/2007' ORDER BY pbs.EffDate DESC, pba.SortID, pba.ID

the problem here is that I want to put the result of the quesry in the () after TOP into the statement but it is not working.

Not the best at T-SQL, but I do know a thing or two.

evidica
Starting Member

17 Posts

Posted - 2007-03-08 : 17:44:59

well I came up with a solution, sadly I couldnt use SQL all the way, had to do one extra query, but no biggie.

Not the best at T-SQL, but I do know a thing or two.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources