Newbie - Help required with Query (sample tables/data included)

** Also posted to: comp.databases.ms-sqlserver
Hi all,
I have 2 tables 'ZoneData' and 'ZoneUser'. The 'ZoneUser' table has a
column that refers
to a 'ZoneData' row. Table definitions and sample data are:
CREATE TABLE [dbo].[ZoneData](
[ZoneId] [int] NOT NULL,
[ZoneName] [nchar](10) NOT NULL,
[IsDefault] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ZoneUser](
[Id1] [int] NOT NULL,
[Id2] [int] NOT NULL,
[ZoneId] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,2)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,4)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,5)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,1)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (102,3,5)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (103,4,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (104,5,2)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (104,5,5)
GO
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (1,'Zone 1',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (2,'Zone 2',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (3,'Zone 3',1)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (4,'Zone 4',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (5,'Zone 5',0)
GO
Running the Query:
SELECT ZoneUser.*, ZoneData.IsDefault FROM ZoneData INNER JOIN ZoneUser
ON ZoneData.ZoneId = ZoneUser.ZoneId
Displays the data:
Id1 Id2 ZoneId IsDefault
100 1 3 1
100 1 2 0
100 1 4 0
101 2 5 0
101 2 1 0
101 2 3 1
102 3 5 0
103 4 3 1
104 5 2 0
104 5 5 0
For each combination of 'Id1' and 'Id2' there may be 0 or more rows
with different 'ZoneId' values.
The Problem: I would like to create a query that could return a row for
every
'Id1' and 'Id2' combination that showed the FIRST (in terms of 'first
found in
database' - not as a result of some sort order) row where 'IsDefault ==
0'
Using the above data, the output would be:
100 1 2 0 < ZoneId '2' id 1st row where 'IsDefault
# 1'
101 2 5 0
102 3 5 0
104 5 2 0
* There is no row returned for Id1=103 Id2=4 as there is no row where
'IsDefault = 0'
Has anyone got an idea on how I might do this? I'm using SQL Server
2005
cheers,
davedmm
Thanks for DDL.
I have added rowid column as IDENTITY property to ZoneUser table
CREATE TABLE [dbo].[ZoneData](
[ZoneId] [int] NOT NULL,
[ZoneName] [nchar](10) NOT NULL,
[IsDefault] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ZoneUser](rowid INT NOT NULL IDENTITY(1,1),
[Id1] [int] NOT NULL,
[Id2] [int] NOT NULL,
[ZoneId] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,2)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,4)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,5)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,1)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (102,3,5)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (103,4,3)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (104,5,2)
INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (104,5,5)
GO
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (1,'Zone 1',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (2,'Zone 2',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (3,'Zone 3',1)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (4,'Zone 4',0)
INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (5,'Zone 5',0)
GO
CREATE VIEW myview
AS
SELECT ZoneUser.*, ZoneData.IsDefault
FROM ZoneData INNER JOIN ZoneUser
ON ZoneData.ZoneId = ZoneUser.ZoneId
WHERE IsDefault=0
SELECT * FROM
(
SELECT *,(SELECT COUNT(*) FROM myview v
WHERE v.Id1=myview.Id1 AND v.rowId<=myview.rowId ) rnk
FROM myview
) AS Der WHERE rnk=1
ORDER BY id1
DROP VIEW myview
DROP TABLE ZoneData,ZoneUser
"dmm" <stope19@.optusnet.com.au> wrote in message
news:1141619306.147227.283440@.v46g2000cwv.googlegroups.com...
> ** Also posted to: comp.databases.ms-sqlserver
> Hi all,
> I have 2 tables 'ZoneData' and 'ZoneUser'. The 'ZoneUser' table has a
> column that refers
> to a 'ZoneData' row. Table definitions and sample data are:
>
> CREATE TABLE [dbo].[ZoneData](
> [ZoneId] [int] NOT NULL,
> [ZoneName] [nchar](10) NOT NULL,
> [IsDefault] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[ZoneUser](
> [Id1] [int] NOT NULL,
> [Id2] [int] NOT NULL,
> [ZoneId] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,3)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,2)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (100,1,4)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,5)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,1)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (101,2,3)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (102,3,5)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (103,4,3)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (104,5,2)
> INSERT INTO ZoneUser (Id1,Id2,ZoneId) VALUES (104,5,5)
> GO
>
> INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (1,'Zone 1',0)
> INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (2,'Zone 2',0)
> INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (3,'Zone 3',1)
> INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (4,'Zone 4',0)
> INSERT INTO ZoneData (ZoneId,ZoneName,IsDefault) VALUES (5,'Zone 5',0)
> GO
>
> Running the Query:
>
> SELECT ZoneUser.*, ZoneData.IsDefault FROM ZoneData INNER JOIN ZoneUser
> ON ZoneData.ZoneId = ZoneUser.ZoneId
>
> Displays the data:
>
> Id1 Id2 ZoneId IsDefault
> 100 1 3 1
> 100 1 2 0
> 100 1 4 0
> 101 2 5 0
> 101 2 1 0
> 101 2 3 1
> 102 3 5 0
> 103 4 3 1
> 104 5 2 0
> 104 5 5 0
>
> For each combination of 'Id1' and 'Id2' there may be 0 or more rows
> with different 'ZoneId' values.
>
> The Problem: I would like to create a query that could return a row for
> every
> 'Id1' and 'Id2' combination that showed the FIRST (in terms of 'first
> found in
> database' - not as a result of some sort order) row where 'IsDefault ==
> 0'
> Using the above data, the output would be:
>
> 100 1 2 0 < ZoneId '2' id 1st row where 'IsDefault
> # 1'
> 101 2 5 0
> 102 3 5 0
> 104 5 2 0
>
> * There is no row returned for Id1=103 Id2=4 as there is no row where
> 'IsDefault = 0'
>
> Has anyone got an idea on how I might do this? I'm using SQL Server
> 2005
>
> cheers,
> dave
>|||Thanks for the help Uri. Much appreciated.
cheers,
dave