msiedle
asked on
SQL2005: DISTINCT with ROW_NUMBER() ...multiple results
Hi all
I had this query working when I used default paging in my ASP.NET datagrids
SELECT
DISTINCT APE.*
FROM
eHR_Accred_Practice_Exampl e APE INNER JOIN
eHR_Accred_Principle AP ON APE.principle = AP.principle_id INNER JOIN
eHR_Accred_Quality_Area AQA ON AP.quality_area = AQA.area_id
WHERE
APE.org_code = '000037'
AND AQA.area_abbr = 'QA2'
... but now I want to use the new SQL2005 row_number() feature and use custom paging in my ASp.NET datagrids to only return a set number of results (Example results 0 - 10 page 1, 10-20 page 2 etc.).
So I wrote this and I can't seem to integrate the 'DISTINCT' in anywhere to stop the query returning multiple results of exactly the same thing (minus the RowNum of course ...that's incrementing perfectly as it should):
SELECT
RowNum,
example_id,
date_of_exam,
principle,
description,
example,
notes,
org_code
FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY eHR_Accred_Practice_Exampl e.example_ id) as RowNum,
eHR_Accred_Practice_Exampl e.example_ id As example_id,
eHR_Accred_Practice_Exampl e.date_of_ exam As date_of_exam,
eHR_Accred_Practice_Exampl e.principl e As principle,
eHR_Accred_Practice_Exampl e.descript ion As description,
eHR_Accred_Practice_Exampl e.example As example,
eHR_Accred_Practice_Exampl e.notes As notes,
eHR_Accred_Practice_Exampl e.org_code As org_code
FROM
eHR_Accred_Practice_Exampl e
LEFT JOIN eHR_Accred_Principle ON eHR_Accred_Practice_Exampl e.principl e = eHR_Accred_Principle.princ iple_id
LEFT JOIN eHR_Accred_Quality_Area ON eHR_Accred_Principle.quali ty_area = eHR_Accred_Quality_Area.ar ea_id
WHERE
eHR_Accred_Practice_Exampl e.org_code = '000037'
AND eHR_Accred_Quality_Area.ar ea_abbr = 'QA2'
) as TableInfo
WHERE RowNum BETWEEN 0 AND 10
THAT query returns these multiple results:
1, 000033, NULL, 000021, '', '', '', 000037
2, 000033, NULL, 000021, '', '', '', 000037
3, 000033, NULL, 000021, '', '', '', 000037
4, 000033, NULL, 000021, '', '', '', 000037
... etc
I need the results to be unique ...as in only show me ONE of the example_ids 000033, then the next like so:
1, 000033, NULL, 000021, '', '', '', 000037
2, 000034, NULL, 000021, 'testing', '', '', 000037
...can anyone help please?
Here's my schema for all the tables mentioned above:
CREATE TABLE [dbo].[eHR_Accred_Practice _Example](
[example_id] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_ AS NOT NULL,
[date_of_exam] [datetime] NULL,
[principle] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_ AS NOT NULL,
[description] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[example] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[notes] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[org_code] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_ AS NOT NULL,
CONSTRAINT [PK_eHR_Accred_Practice_Ex ample] PRIMARY KEY CLUSTERED
(
[example_id] ASC,
[org_code] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[eHR_Accred_Principl e](
[principle_id] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_ AS NOT NULL,
[principle_name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[principle_description] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[quality_area] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[org_code] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_ AS NOT NULL,
[isDefault] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
CONSTRAINT [PK_eHR_Accred_Principle] PRIMARY KEY CLUSTERED
(
[principle_id] ASC,
[org_code] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[eHR_Accred_Quality_ Area](
[area_id] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_ AS NOT NULL,
[area_name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[area_abbr] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[org_code] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_ AS NOT NULL,
[isDefault] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
CONSTRAINT [PK_eHR_Accred_Quality_Are a] PRIMARY KEY CLUSTERED
(
[area_id] ASC,
[org_code] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Many thanks :-)
Mark
I had this query working when I used default paging in my ASP.NET datagrids
SELECT
DISTINCT APE.*
FROM
eHR_Accred_Practice_Exampl
eHR_Accred_Principle AP ON APE.principle = AP.principle_id INNER JOIN
eHR_Accred_Quality_Area AQA ON AP.quality_area = AQA.area_id
WHERE
APE.org_code = '000037'
AND AQA.area_abbr = 'QA2'
... but now I want to use the new SQL2005 row_number() feature and use custom paging in my ASp.NET datagrids to only return a set number of results (Example results 0 - 10 page 1, 10-20 page 2 etc.).
So I wrote this and I can't seem to integrate the 'DISTINCT' in anywhere to stop the query returning multiple results of exactly the same thing (minus the RowNum of course ...that's incrementing perfectly as it should):
SELECT
RowNum,
example_id,
date_of_exam,
principle,
description,
example,
notes,
org_code
FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY eHR_Accred_Practice_Exampl
eHR_Accred_Practice_Exampl
eHR_Accred_Practice_Exampl
eHR_Accred_Practice_Exampl
eHR_Accred_Practice_Exampl
eHR_Accred_Practice_Exampl
eHR_Accred_Practice_Exampl
eHR_Accred_Practice_Exampl
FROM
eHR_Accred_Practice_Exampl
LEFT JOIN eHR_Accred_Principle ON eHR_Accred_Practice_Exampl
LEFT JOIN eHR_Accred_Quality_Area ON eHR_Accred_Principle.quali
WHERE
eHR_Accred_Practice_Exampl
AND eHR_Accred_Quality_Area.ar
) as TableInfo
WHERE RowNum BETWEEN 0 AND 10
THAT query returns these multiple results:
1, 000033, NULL, 000021, '', '', '', 000037
2, 000033, NULL, 000021, '', '', '', 000037
3, 000033, NULL, 000021, '', '', '', 000037
4, 000033, NULL, 000021, '', '', '', 000037
... etc
I need the results to be unique ...as in only show me ONE of the example_ids 000033, then the next like so:
1, 000033, NULL, 000021, '', '', '', 000037
2, 000034, NULL, 000021, 'testing', '', '', 000037
...can anyone help please?
Here's my schema for all the tables mentioned above:
CREATE TABLE [dbo].[eHR_Accred_Practice
[example_id] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_
[date_of_exam] [datetime] NULL,
[principle] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_
[description] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_
[example] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_
[notes] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_
[org_code] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_
CONSTRAINT [PK_eHR_Accred_Practice_Ex
(
[example_id] ASC,
[org_code] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[eHR_Accred_Principl
[principle_id] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_
[principle_name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_
[principle_description] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_
[quality_area] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_
[org_code] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_
[isDefault] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_
CONSTRAINT [PK_eHR_Accred_Principle] PRIMARY KEY CLUSTERED
(
[principle_id] ASC,
[org_code] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[eHR_Accred_Quality_
[area_id] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_
[area_name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_
[area_abbr] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_
[org_code] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_
[isDefault] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_
CONSTRAINT [PK_eHR_Accred_Quality_Are
(
[area_id] ASC,
[org_code] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Many thanks :-)
Mark
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cheers,
Mark