need help with mssql 2000 select query

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)

need help with mssql 2000 select query

Author

Topic

lew26
Starting Member

5 Posts

Posted - 2007-11-06 : 15:48:07

I have a select query i am working on i need help with. The problem is it returns two of duplicates records. Because of the way the back end is design i am limited to what i can do with the select query. For example in code it creates dynamic queries, the dynamic addition to the select query are basically the between statements in the query. Also the site is using asp tables instead of grids so it is dependant on the order of the select statement. Making changes to the order of the selected column will mean redoing an entire page 3000+ lines.

What i need to happen is i would like to select distinct row based on the column [when] (see below). the stage is always duplicate (for example there are other row which didn't match this criteria but have the same stage name), same as [band], Location, Promo, Location_website, and Sets media values depending on the stage id.

So the question i need answered is how can i select distinct columns based on the [when] column?

Select Query:
SELECT Stage, [When], [Band], Length, Promo, Location, When2, setkey,
EntMedia, SetsMedia, Location_Website, Pic12, Stage_Website, BandID, StageID
FROM PromoSchedule
WHERE (See_In_Promo = 1 and Hide_From_Promos = 0)
AND ([When] between '11/24/2007 06:00 AM'
And '12/08/2007 06:00 AM')
AND (WhereID = 90)
AND (WhereID <> 8)
Order by Location, Stage, [When]

DigitalSolvers
Yahoo: DigitalSolvers
MSN Live: lew26@msn.com
Skype: DigitalSolvers

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-06 : 15:51:09

Aggregate the WHEN column and GROUP BY the rest of them?

E 12°55'05.25"
N 56°04'39.16"