Converting a MySQL multiple column subselect to SQL Server

3 Mar 2006 6:29 AM

chris_g

I am adding SQL Server support to an application that currently uses
MySQL 4.1.

I have a table that looks like this in SQL Server 8.0:

CREATE TABLE dbo.sales_estimates
    (
    ID int NOT NULL,
    YearMonth datetime NULL,
    CountryCode char(3) NULL,
    StoreCode int NULL,
    SalesEstimate decimal(18, 0) NULL,
    UserID int NULL,
    DateTimeStamp datetime NULL
    ) ON [PRIMARY]
GO
ALTER TABLE dbo.sales_estimates ADD CONSTRAINT
    PK_Table1 PRIMARY KEY CLUSTERED
    (
    ID
    ) ON [PRIMARY]
GO

It contains multiple sales estimates for stores, eg different users can
enter their own SalesEstimate for each store's monthly sales.

I want to select the most recent sales estimate for each store for a
given month.

In MySQL 4.1 I can do this by with the folling nested selects:

select YearMonth, CountryCode, StoreCode, SalesEstimate from
store_estimates
where (YearMonth,CountryCode, StoreCode, DateTimeStamp)
    in (select YearMonth, State, StoreCode , max(DateTimeStamp)
        from store_estimates
        where YearMonth ='2006-01-1'
        group by YearMonth, State, StoreCode)

I'd like to write a similar statement for SQL Server (version 8.0) if
this is possible, but it appears that I can't have multiple rows in
subselects. Ideally I'd like to find a simpler query that works for
both DBs.

3 Mar 2006 1:17 PM

markc600

SQL Server doesn't support the syntax, I believe it's referred
to as 'row constructors'

You can do this instead

select a.YearMonth, a.CountryCode, a.StoreCode, a.SalesEstimate
from store_estimates a
inner join (select YearMonth, State, StoreCode , max(DateTimeStamp)
   from store_estimates
   where YearMonth ='2006-01-1'
   group by YearMonth, State, StoreCode) b(YearMonth,CountryCode,
StoreCode, DateTimeStamp)
on a.YearMonth=b.YearMonth and a.CountryCode=b.CountryCode
and a.StoreCode=b.StoreCode and a.DateTimeStamp=b.DateTimeStamp

You can also achieve the same results with an EXISTS clause.