distinct row count in a table.

Hi,

I want a count of distinct rows in a table through a single query -- is it possible?

eg.

table-

create table ch1 (a int, b int, c int, d int)

insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,3,4,5)

Here distinct row count in a table is 3 which I want to achieve thro a query.

if I do

select count(distinct a) from ch1 it works fine and gives me output as 2.

but this is not working

select count(distinct a,b,c,d) from ch1 - any workaround to find the distinct row count in a table??

Please reply.

Cheers!
Ram.Hi,

I want a count of distinct rows in a table through a single query -- is it possible?

eg.

table-

create table ch1 (a int, b int, c int, d int)

insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,3,4,5)

Here distinct row count in a table is 3 which I want to achieve thro a query.

if I do

select count(distinct a) from ch1 it works fine and gives me output as 2.

but this is not working

select count(distinct a,b,c,d) from ch1 - any workaround to find the distinct row count in a table??

Please reply.

Cheers!
Ram.|||Try this...

SELECT COUNT(*)
FROM
(SELECT DISTINCT * FROM ch1)ch1|||Or
SELECT COUNT(DISTINCT *) AS Distinct_Rows FROM ch1|||:shocked:
select sum(case when count(*)>1 then 1 else 1 end)
from ch1 group by a,b,c,d|||threads merged

ramshree, please do not post the same question into multiple forums|||I have an example below: You should use the "having" clause.
db2 "select serialno,count(*) from svcprd.bcbs_unix_sysinfo group by serialno having count(*)>1|||I have an example below: You should use the "having" clause.
db2 "select serialno,count(*) from svcprd.bcbs_unix_sysinfo group by serialno having count(*)>1I think that you're "close, but no banana" on this... The code that you posted will actually count the non-distinct rows (how many rows have at least one duplicated row elsewhere).

-PatP

DISTINCT question

Hello all!

I understand that if you place a DISTINCT in a SELECT statement, it will return only unique values of that row, depending how many coumns you have. So look at this.

Code Snippet

SELECT DISTINCT Active_Orders.First_Name, Active_Orders.Last_Name, Active_Orders.Account_Number, Active_Orders.Service_Date_Time, Active_Orders.Stat,

Order_Status.Status, Active_Orders.Order_ID, Active_Orders.Age, Active_Orders.DOB, Active_Orders.Rm_Desc, Active_Orders.Check_Out,

Active_Orders.Remarks, Locations.Loct_Desc, Active_Orders.FilePath, Active_Orders.Misc, Active_Orders.File_Date_Time

FROM Active_Orders INNER JOIN

Order_Status ON Active_Orders.Status_ID = Order_Status.Status_ID INNER JOIN

Locations ON Active_Orders.Location_ID = Locations.Location_ID

WHERE (Order_Status.Status = 'InProcess') OR

(Order_Status.Status = 'Pending') OR

(Order_Status.Status = 'OnHold') OR

(Order_Status.Status = 'C1') OR

(Order_Status.Status = 'C2') OR

(Order_Status.Status = 'C3') OR

(Order_Status.Status = 'C4') OR

(Order_Status.Status = 'C5') OR

(Order_Status.Status = 'C6') OR

(Order_Status.Status = 'C7')

ORDER BY Active_Orders.Stat DESC, Order_Status.Status DESC

I need to only return rows with unique Active_Orders.Account_Number. The way it is now, even if I have the same Account_Number, it will return both, because according the statment above, it still is a unique record despite the fact the account number is the same. The Order_ID is diffrent, Service_Date_time is different, etc.

So, How can I return all the fields above, but eliminate account numbers are the same?

Thanks!!

Rudy

Your query currently returns distinct rows.

You want it to return fewer rows than it does--specifically--only one row for each Account_Number. The question then is "Which rows do you want?" Unfortunately, there is no "any one but I don't care which one" aggregate in SQL, so you have to give a precise condition that can be evaluated in SQL to describe for a given account number, which row you want to see out of the many you're currently getting.

You might end up with something like (if your rule "the one with the latest File_Date_Time" value)

with YourQuery as (

rank() over (partition by Account_Number order by File_Date_Time desc) as rk,

<the rest of your current query>

)

select * from YourQuery as Q1

where rk = 1

If you aren't using SQL Server 2005, it's harder to write, but can still be done. In that case, you express this:

select <columns>

from <wherever>

where <whatever> as T1

and NOT EXISTS (

select *

from <same wherever> as T2

where <same whatever>

and T2.account_number = T1.account_number

and T2.File_Date_Time > T1.File_Date_Time

)

Steve Kass

Drew University

http://www.stevekass.com

|||

Just Try a Group statement instead

like this

SELECT a.* FROM mytable AS a

WHERE a.Account_Number IN(

SELECT B.Account_Number FROM (

SELECT Account_Number , count(Account_Number ) AS ACC

FROM mytable GROUP BY Account_Number

) AS B

WHERE B.ACC=1

)

So You'll get only the rows of The Account_numbers represented once in the table

I hope this will help

Best regards

Raimund

|||

Thank you guys for your suggestion!

Steve, I am using SQL 2005. I like the idea of the rule with the time. I'm actually going to have to put that in place anyway. If an order has the same account number within 2 minutes of the FILE_TIME, then only show one account number.

So do I put "with YourQuery as (

rank() over (partition by Account_Number order by File_Date_Time desc) as rk,

this on the very top of my procedure?

Then put in my query in,

then put in "select * from YourQuery as Q1

where rk = 1"

Sorry, I'm just sure of the order of this.

Thanks!

Rudy

DISTINCT question

Hello all!

I understand that if you place a DISTINCT in a SELECT statement, it will return only unique values of that row, depending how many coumns you have. So look at this.

Code Snippet

SELECT DISTINCT Active_Orders.First_Name, Active_Orders.Last_Name, Active_Orders.Account_Number, Active_Orders.Service_Date_Time, Active_Orders.Stat,

Order_Status.Status, Active_Orders.Order_ID, Active_Orders.Age, Active_Orders.DOB, Active_Orders.Rm_Desc, Active_Orders.Check_Out,

Active_Orders.Remarks, Locations.Loct_Desc, Active_Orders.FilePath, Active_Orders.Misc, Active_Orders.File_Date_Time

FROM Active_Orders INNER JOIN

Order_Status ON Active_Orders.Status_ID = Order_Status.Status_ID INNER JOIN

Locations ON Active_Orders.Location_ID = Locations.Location_ID

WHERE (Order_Status.Status = 'InProcess') OR

(Order_Status.Status = 'Pending') OR

(Order_Status.Status = 'OnHold') OR

(Order_Status.Status = 'C1') OR

(Order_Status.Status = 'C2') OR

(Order_Status.Status = 'C3') OR

(Order_Status.Status = 'C4') OR

(Order_Status.Status = 'C5') OR

(Order_Status.Status = 'C6') OR

(Order_Status.Status = 'C7')

ORDER BY Active_Orders.Stat DESC, Order_Status.Status DESC

I need to only return rows with unique Active_Orders.Account_Number. The way it is now, even if I have the same Account_Number, it will return both, because according the statment above, it still is a unique record despite the fact the account number is the same. The Order_ID is diffrent, Service_Date_time is different, etc.

So, How can I return all the fields above, but eliminate account numbers are the same?

Thanks!!

Rudy

Your query currently returns distinct rows.

You want it to return fewer rows than it does--specifically--only one row for each Account_Number. The question then is "Which rows do you want?" Unfortunately, there is no "any one but I don't care which one" aggregate in SQL, so you have to give a precise condition that can be evaluated in SQL to describe for a given account number, which row you want to see out of the many you're currently getting.

You might end up with something like (if your rule "the one with the latest File_Date_Time" value)

with YourQuery as (

rank() over (partition by Account_Number order by File_Date_Time desc) as rk,

<the rest of your current query>

)

select * from YourQuery as Q1

where rk = 1

If you aren't using SQL Server 2005, it's harder to write, but can still be done. In that case, you express this:

select <columns>

from <wherever>

where <whatever> as T1

and NOT EXISTS (

select *

from <same wherever> as T2

where <same whatever>

and T2.account_number = T1.account_number

and T2.File_Date_Time > T1.File_Date_Time

)

Steve Kass

Drew University

http://www.stevekass.com

|||

Just Try a Group statement instead

like this

SELECT a.* FROM mytable AS a

WHERE a.Account_Number IN(

SELECT B.Account_Number FROM (

SELECT Account_Number , count(Account_Number ) AS ACC

FROM mytable GROUP BY Account_Number

) AS B

WHERE B.ACC=1

)

So You'll get only the rows of The Account_numbers represented once in the table

I hope this will help

Best regards

Raimund

|||

Thank you guys for your suggestion!

Steve, I am using SQL 2005. I like the idea of the rule with the time. I'm actually going to have to put that in place anyway. If an order has the same account number within 2 minutes of the FILE_TIME, then only show one account number.

So do I put "with YourQuery as (

rank() over (partition by Account_Number order by File_Date_Time desc) as rk,

this on the very top of my procedure?

Then put in my query in,

then put in "select * from YourQuery as Q1

where rk = 1"

Sorry, I'm just sure of the order of this.

Thanks!

Rudy

sql

Distinct For Only Some Columns In A Row

Hello,
I'm stuck, and I have earnestly searched for an answer to this problem
during the past few hours...
I'm trying to work with this query:
SELECT T1.UserID, T2.MiscID
FROM Table2 T2 INNER JOIN Table1 T1 ON T2.ID = T1.ID
WHERE (T2.Category IN (2, 3, 4, 5)) AND (T1.ZipCode IN (22201,22202,22203))
Based on the data in the tables, this query generates the following result:
UserID MiscID
10 105
10 107
11 109
11 120
11 122
The problem is that I do not want duplicate UserID's (I show only 10 and 11,
but the table actually has many rows with duplicate UserID values). Instead,
I want unique (distinct) UserID's and the lowest MiscID for each row. I
would therefore like the result set to look like this:
UserID MiscID
10 105
11 109
I would appreciate any assistance.
Thanks,
JimSELECT T1.UserID, Min(T2.MiscID) As MiscId
FROM Table2 T2 INNER JOIN Table1 T1 ON T2.ID = T1.ID
WHERE (T2.Category IN (2, 3, 4, 5)) AND (T1.ZipCode IN (22201,22202,22203))
GROUP BY T1.UserID
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Jim Little" <123@.yahoo.com> wrote in message
news:j6UTd.52885$Bx5.16249@.fe1.texas.rr.com...
> Hello,
> I'm stuck, and I have earnestly searched for an answer to this problem
> during the past few hours...
> I'm trying to work with this query:
> SELECT T1.UserID, T2.MiscID
> FROM Table2 T2 INNER JOIN Table1 T1 ON T2.ID = T1.ID
> WHERE (T2.Category IN (2, 3, 4, 5)) AND (T1.ZipCode IN
> (22201,22202,22203))
> Based on the data in the tables, this query generates the following
> result:
> UserID MiscID
> 10 105
> 10 107
> 11 109
> 11 120
> 11 122
> The problem is that I do not want duplicate UserID's (I show only 10 and
> 11, but the table actually has many rows with duplicate UserID values).
> Instead, I want unique (distinct) UserID's and the lowest MiscID for each
> row. I would therefore like the result set to look like this:
> UserID MiscID
> 10 105
> 11 109
> I would appreciate any assistance.
> Thanks,
> Jim
>
>|||Thank you very much. That worked perfectly!
Although I made a "stripped-down" version of the problem so I could post a
focussed question, I actually need to join across four table and draw data
from each of them. However, I still want to "group" on only the first UserID
column. There are 12 additional columns that need to appear in the output.
I'll experiment a bit further and I'll post another follow-up message on
this thread if I get stuck again.
Meanwhile, thanks for the quick and helpful reply.
-- Jim
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:uYpCUn8GFHA.2156@.TK2MSFTNGP09.phx.gbl...
> SELECT T1.UserID, Min(T2.MiscID) As MiscId
> FROM Table2 T2 INNER JOIN Table1 T1 ON T2.ID = T1.ID
> WHERE (T2.Category IN (2, 3, 4, 5)) AND (T1.ZipCode IN
> (22201,22202,22203))
> GROUP BY T1.UserID
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "Jim Little" <123@.yahoo.com> wrote in message
> news:j6UTd.52885$Bx5.16249@.fe1.texas.rr.com...
>|||OK--I found a way to better express the problem I was having, but I need
first to add another column...
The Group By suggested by Roji does give me the following as output:
UserID MiscID
10 105
11 109
And that's what I wanted in my first question. But I had simplified my
question. In actuality, I require additional columns in my query
results--but I still want only the unique UserID keys. My query is now:
SELECT T1.UserID, MIN(T2.MiscID) AS MiscID, T2.Age
FROM Table1 T1 INNER JOIN
Table2 T2 ON T1.ID = T2.ID
WHERE (T2.Category IN (2, 3, 4, 5))
AND (T1.ZipCode IN (22201,22202,22203))
GROUP BY T1.UserID, T2.Age
This is giving me the following results:
UserID MiscID Age
10 105 32
10 106 37
11 109 50
11 112 52
11 118 42
What I require of my results is only the minimum (MIN) MiscID for each
UserID--and its Age:
UserID MiscID Age
10 105 32
11 109 50
I need all three columns because they are returned to a third-tier (client)
application, but I can only have unique UserID values.
I think once I understand how to do this, I will be able to tackle similar
issues that would arise when I add more columns. If there are many ways to
approach this problem, I would prefer a more complex method that would
perform best. This is a query that will be used many times in an enterprise
environment, so response time is key.
Thank you so much for any help.
-- Jim|||I have posted a new message entitled "Distinct and Group By".
Thank you,
-- Jim

Distinct and Group By

*** This is a follow-up to my recent post "Distinct For Only Some Columns In
A Row".
Recently, I posted a question. That question was answered, but a new
question surfaced immediately afterwards. What follows is a combination of
my former post and the new question...
I'm trying to work with this query:
SELECT T1.UserID, T2.MiscID
FROM Table2 T2 INNER JOIN Table1 T1 ON T2.ID = T1.ID
WHERE (T2.Category IN (2, 3, 4, 5)) AND (T1.ZipCode IN (22201,22202,22203))
Based on the data in the tables, this query generates the following result:
UserID MiscID
10 105
10 107
11 109
11 120
11 122
The problem is that I do not want duplicate UserID's (I show only 10 and 11,
but the table actually has many rows with duplicate UserID values). Instead,
I want unique (distinct) UserID's and the lowest MiscID for each row. I
would therefore like the result set to look like this:
Using Group By, I get this query:
SELECT T1.UserID, MIN(T2.MiscID) AS MiscID
FROM Table1 T1 INNER JOIN
Table2 T2 ON T1.ID = T2.ID
WHERE (T2.Category IN (2, 3, 4, 5))
AND (T1.ZipCode IN (22201,22202,22203))
GROUP BY T1.UserID
UserID MiscID
10 105
11 109
This solves the need for non-duplicated UserID's. But now I require an
additional column in my query
results--but I still want only the unique UserID keys. My query is now:
SELECT T1.UserID, MIN(T2.MiscID) AS MiscID, T2.Age
FROM Table1 T1 INNER JOIN
Table2 T2 ON T1.ID = T2.ID
WHERE (T2.Category IN (2, 3, 4, 5))
AND (T1.ZipCode IN (22201,22202,22203))
GROUP BY T1.UserID, T2.Age
This is giving me the following results:
UserID MiscID Age
10 105 32
10 106 37
11 109 50
11 112 52
11 118 42
What I require of my results is only the minimum (MIN) MiscID for each
UserID--and its Age:
UserID MiscID Age
10 105 32
11 109 50
I need all three columns because they are returned to a third-tier (client)
application, but I can only have unique UserID values.
I think once I understand how to do this, I will be able to tackle similar
issues that would arise when I add more columns. If there are many ways to
approach this problem, I would prefer a more complex method that would
perform best. This is a query that will be used many times in an enterprise
environment, so response time is key.
Thank you so much for any help.
-- JimWe'd need to see your DDL for the tables involved. Here's a guess:
SELECT
x.UserID, x.MiscID, t3.Age
FROM
(
SELECT T1.UserID, MIN(T2.MiscID) AS MiscID
FROM Table1 T1 INNER JOIN
Table2 T2 ON T1.ID = T2.ID
WHERE (T2.Category IN (2, 3, 4, 5))
AND (T1.ZipCode IN (22201,22202,22203))
GROUP BY T1.UserID
) as x
JOIN Table2 t2 on t2.UserID = x.UserID and t2.MiscID = x.MiscID
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Jim Little" <123@.yahoo.com> wrote in message
news:Ly4Ud.60429$911.14807@.fe2.texas.rr.com...
*** This is a follow-up to my recent post "Distinct For Only Some Columns In
A Row".
Recently, I posted a question. That question was answered, but a new
question surfaced immediately afterwards. What follows is a combination of
my former post and the new question...
I'm trying to work with this query:
SELECT T1.UserID, T2.MiscID
FROM Table2 T2 INNER JOIN Table1 T1 ON T2.ID = T1.ID
WHERE (T2.Category IN (2, 3, 4, 5)) AND (T1.ZipCode IN (22201,22202,22203))
Based on the data in the tables, this query generates the following result:
UserID MiscID
10 105
10 107
11 109
11 120
11 122
The problem is that I do not want duplicate UserID's (I show only 10 and 11,
but the table actually has many rows with duplicate UserID values). Instead,
I want unique (distinct) UserID's and the lowest MiscID for each row. I
would therefore like the result set to look like this:
Using Group By, I get this query:
SELECT T1.UserID, MIN(T2.MiscID) AS MiscID
FROM Table1 T1 INNER JOIN
Table2 T2 ON T1.ID = T2.ID
WHERE (T2.Category IN (2, 3, 4, 5))
AND (T1.ZipCode IN (22201,22202,22203))
GROUP BY T1.UserID
UserID MiscID
10 105
11 109
This solves the need for non-duplicated UserID's. But now I require an
additional column in my query
results--but I still want only the unique UserID keys. My query is now:
SELECT T1.UserID, MIN(T2.MiscID) AS MiscID, T2.Age
FROM Table1 T1 INNER JOIN
Table2 T2 ON T1.ID = T2.ID
WHERE (T2.Category IN (2, 3, 4, 5))
AND (T1.ZipCode IN (22201,22202,22203))
GROUP BY T1.UserID, T2.Age
This is giving me the following results:
UserID MiscID Age
10 105 32
10 106 37
11 109 50
11 112 52
11 118 42
What I require of my results is only the minimum (MIN) MiscID for each
UserID--and its Age:
UserID MiscID Age
10 105 32
11 109 50
I need all three columns because they are returned to a third-tier (client)
application, but I can only have unique UserID values.
I think once I understand how to do this, I will be able to tackle similar
issues that would arise when I add more columns. If there are many ways to
approach this problem, I would prefer a more complex method that would
perform best. This is a query that will be used many times in an enterprise
environment, so response time is key.
Thank you so much for any help.
-- Jim|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
In particular, you have a "magical universal" id column in both tables
and a truly vague "misc_id" in another. That is absurd, since an
identifier has to identify a particular kind of entity.
The names table1 and Table2 give us no hint as to what they mean in the
data model, either. Can we get better specs?|||OK--Fair enough. I posted the "generic" tables because I'm not authorized to
give the actual ones. However, let me create some tables that illustrate my
issue and I think it could benefit everyone.
I'll upload those very soon.
Thanks,
-- Jim|||Hi Jim
Assuming you want the lowest ImageNo per user. Two possible (untested) ways:
SELECT T.UserID AS Expr1,
T.FirstName AS Expr2,
T.LastName AS Expr3,
M.LastLogon AS Expr4,
I.Description AS Expr5,
I.ImageNo AS Expr6,
I.PixelHeight AS Expr7,
I.PixelWidth AS Expr8,
I.FileName AS Expr9
FROM dbo.T_Images I
JOIN dbo.T_Members M ON I.UserID = M.UserID
JOIN dbo.T_Users U ON I.UserID = U.UserID
JOIN ( SELECT UserId, MIN(ImageNo) AS ImageNo FROM dbo.T_Images GROUP BY
UserId ) L ON I.UserID = L.UserID AND I.ImageNo = L.ImageNo
SELECT T.UserID AS Expr1,
T.FirstName AS Expr2,
T.LastName AS Expr3,
M.LastLogon AS Expr4,
I.Description AS Expr5,
I.ImageNo AS Expr6,
I.PixelHeight AS Expr7,
I.PixelWidth AS Expr8,
I.FileName AS Expr9
FROM dbo.T_Images I
JOIN dbo.T_Members M ON I.UserID = M.UserID
JOIN dbo.T_Users U ON I.UserID = U.UserID
WHERE I.ImageNo = ( SELECT MIN(L.ImageNo) FROM dbo.T_Images L WHERE I.UserID
= L.UserID )
You may want to look at example data as insert statements
http://vyaskn.tripod.com/code.htm#inserts
instead of attaching data files.
John
"Jim Little" <123@.yahoo.com> wrote in message
news:Bd6Ud.61154$911.53142@.fe2.texas.rr.com...
> Hello:
> I hope this helps: I've attached an image of the tables involved. I have
> also attached a text document with a Create script (no drops are included
> for safety in case you have the same table names in your db). I pasted the
> attached Create script below for convenience (it is the same as the
> text-file attachment).
> In addition, I attached three data files, one for each data table. These
> are ASCII delimitted format.
> The query I am using is:
> SELECT dbo.T_Users.UserID AS Expr1, dbo.T_Users.FirstName AS Expr2,
> dbo.T_Users.LastName AS Expr3, dbo.T_Members.LastLogon AS Expr4,
> dbo.T_Images.Description AS Expr5,
> dbo.T_Images.ImageNo AS Expr6, dbo.T_Images.PixelHeight AS Expr7,
> dbo.T_Images.PixelWidth AS Expr8,
> dbo.T_Images.FileName AS Expr9
> FROM dbo.T_Images INNER JOIN
> dbo.T_Members ON dbo.T_Images.UserID =
> dbo.T_Members.UserID INNER JOIN
> dbo.T_Users ON dbo.T_Images.UserID =
> dbo.T_Users.UserID
> This is giving me (sorry for the word wrapping):
> Expr1,Expr2,Expr3,Expr4,Expr5,Expr6,Expr
7,Expr8,Expr9
> 1,Jim ,Smith ,2005-01-05 00:00:00.000,Hawaii
> ,1,500,500,hawaii.jpg
> 1,Jim ,Smith ,2005-01-05 00:00:00.000,Grand
> Canyon ,2,350,350,gcanyon.jpg
> 1,Jim ,Smith ,2005-01-05 00:00:00.000,Time
> Square ,3,400,600,timessq.jpg
> 2,Steve ,Jones ,2005-01-22
> 00:00:00.000,Orlando ,2,500,500,orlando.jpg
> 2,Steve ,Jones ,2005-01-22 00:00:00.000,LA
> What I would like to see is:
> Expr1,Expr2,Expr3,Expr4,Expr5,Expr6,Expr
7,Expr8,Expr9
> 1,Jim ,Smith ,2005-01-05 00:00:00.000,Hawaii
> ,1,500,500,hawaii.jpg
> 2,Steve ,Jones ,2005-01-22
> 00:00:00.000,Orlando ,2,500,500,orlando.jpg
>
> Notice that the results I would like to see are unque to the first (Expr1,
> or UserID) column--but I get to see all the other columns that apply to
> that row.
> One idea I had is to first create a Select that selects only the UserIDs.
> Then, use those UserIDs in an "IN" or "EXISTS" clause.
> Thank you for any insights.
> Regards,
> -- Jim
> ===============
>
> CREATE TABLE [dbo].[T_Images] (
> [ImageID] [int] IDENTITY (1, 1) NOT NULL ,
> [UserID] [int] NOT NULL ,
> [ImageNo] [smallint] NOT NULL ,
> [Description] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [PixelHeight] [smallint] NOT NULL ,
> [PixelWidth] [smallint] NOT NULL ,
> [FileName] [char] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[T_Members] (
> [UserID] [int] NOT NULL ,
> [LastLogon] [datetime] NOT NULL ,
> [MemberAlias] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Password] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[T_Users] (
> [UserID] [int] IDENTITY (1, 1) NOT NULL ,
> [FirstName] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LastName] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>|||Looks great--Thank you John. I will try both and I'll post feedback either
way within a day.
Thanks,
-- Jim

Displaying the row number in a query

Hi,
My query is retrieving rows from a table.
All what I need is to display the row number which is simply a consecutive number.
Can any one advise me if there is a direct thing to do this in the SELECT stmt?
I can do it through creating a temp table then add IDENTITY column pla pla pla ..I need a direct way through the query itself.
In Oracle I can use RowNum in the query.

Thanks in advance for all.Is it not doable?

Originally posted by RaedT
Hi,
My query is retrieving rows from a table.
All what I need is to display the row number which is simply a consecutive number.
Can any one advise me if there is a direct thing to do this in the SELECT stmt?
I can do it through creating a temp table then add IDENTITY column pla pla pla ..I need a direct way through the query itself.
In Oracle I can use RowNum in the query.

Thanks in advance for all.|||The short answer is NO. TSQL operations are set based, and no ordering is guaranted by the server unless the developer or programmer specifies it, thus row numbering is pointless because the same query run on two different occasions could result in the same record being assigned different row numbers.

The long answer is YES, if your result set is sorted by a unique key or combination of columns, then you can write a Select statement that loops back on itself and counts the number of records less than each record. This is an expensive query to run and can be difficult to debug, so my recommendation to you would be to use a temporary table (actually, a table variable is more efficient) as long as it suits your needs.

Why do you need the results numbered? While there are some circumstances where this is beneficial, it is often a sign of problems with the database schema or the application design concept.

blindman|||If you don't have already a client, I may consider to put your SELECT statement in the software of a (ADO) client, where you can make use of the AbsolutePosition property of a recordset.

Displaying the query result in one row

Hi all,

I have two M:M tables, A and B, which are connected by a 3rd link table C.
Each row of A might be connected to 0 to 10 row(s) of B.

A (ID, AName)
C (A.ID, B.ID)
B (ID, Bname)

And when I query AName = Smith, normally, it returns:

Smith | Hat
Smith | Coat
Smith | Shoes

My question:
Is there a way to DISPLAY this result in ONE row without changing the tables structure?
Like -> Smith | Hat | Coat | Shoes

Any comment and help would be appreciated.

Thanks
SamYes, but not using pure SQL. You will have to write application code to query the data order by ANAME and then keep concatenating the BNAME values together until the ANAME changes.|||Is there a way to DISPLAY this result in ONE row without changing the tables structure?
Like -> Smith | Hat | Coat | Shoesthere sure is, but only in mysql version 4.1+, and sybase ase|||there sure is, but only in mysql version 4.1+, and sybase ase
... or Oracle 9i like this (http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:2196162600402).|||... or Oracle 9i like this (http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:2196162600402).W TF OMG LOL

that's horrible!!

:cool:|||True, but at least Tom Kyte has done all the work for us. All we have to do is install his STRAGG function once, make it public, and from then on just use it, i.e.:

select deptno, stragg(ename)
from emp
group by deptno;

(But it would be neat if it came built-in to the DBMS I agree).|||Thanks a lot Tony and Rudy.

I thought there might be a SQL trick (!) to do that and I would put myself in disadvantage if I didn't ask you about that.

I will try this again when I start my application development. Any reference, link, or hint on this subject would be helpful for me. I am not even sure what such a process is called, "multi-column to one-row"? :D

Rudy, I was wondering if you could address the section of MySQL manual for doing that job in MySQL 4.1+ as you mentioned. (I am going to use MySQL 4.1.x and PHP)

Thanks again
Sam|||it's called "denormalization"

in mysql it's the GROUP_CONCAT function (see 12.9.1 GROUP BY (Aggregate) Functions (http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html))

using your tables A,B,C above, you would write:select AName
, group_concat(BName separator ' | ') as BNames
from A
inner join C ...
group by AName|||Yes! That was what I was looking for.

Thank you very much Rudy. You solved my problem again. Your solution works very well. Especially, with that separator '|' you recommended, the result is very readable.

You are not only very knowledgeable but also an awesome teacher. I must enroll in your "online course SQL for Database-Driven Web Sites". I am sure I will learn a lot from you.

Regards,
Sam

Displaying duplicate row in red color

Hi,
In my report, I have used a table. In the table, I want to display all the
duplicate rows in red color, i.e if Field1 is listed twice in the table
details, then I want to display the duplicate row in red color.
ANy ideas on how to achieve this?
Thanks
--
pmudSet the Color property of the textbox of Field1 to use an expression,
like this:
=IIF( Fields!Field1.Value = Previous( Fields!Field1.Value), "Red",
"Black")
Matt A|||Thanks MattA. This is extremely helpful. :)
--
pmud
"MattA" wrote:
> Set the Color property of the textbox of Field1 to use an expression,
> like this:
> =IIF( Fields!Field1.Value = Previous( Fields!Field1.Value), "Red",
> "Black")
> Matt A
>|||I was wondering if you can refer to the textbox.value from within a property
like:
=iif(textbox98.value > 0, "green", "red")
--
William Stacey [MVP]
"MattA" <mattarabas@.gmail.com> wrote in message
news:1138312093.490398.115610@.g14g2000cwa.googlegroups.com...
| Set the Color property of the textbox of Field1 to use an expression,
| like this:
|
| =IIF( Fields!Field1.Value = Previous( Fields!Field1.Value), "Red",
| "Black")
|
| Matt A
||||=IIF(ReportItems!textbox98.Value > 0, "GREEN", "RED")
Note that scope rules apply to ReportItems collection, so you'll have
scope limitations on being able to use it (not on aggregates, for
example...)
Matt A|||Thanks Matt. Are these processing "rules" layed out in BOL or somewhere.
Cheers!
--
William Stacey [MVP]
"MattA" <mattarabas@.gmail.com> wrote in message
news:1138370026.412815.53510@.g44g2000cwa.googlegroups.com...
| =IIF(ReportItems!textbox98.Value > 0, "GREEN", "RED")
|
| Note that scope rules apply to ReportItems collection, so you'll have
| scope limitations on being able to use it (not on aggregates, for
| example...)
|
| Matt A
|

Displaying data in Columns based on Criteria

I have data in rows that I want to display in columns based on a value in
each row.
Here's an example of the data:
Employee ID Type Code Amount
123 PAY BONUS 1,000
123 PAY SALARY 5,000
123 DED INS 500
123 DED DENTAL 100
123 DED FLEX 50
Here's how I'd like to display the data:
EMPLOYEE CODE AMOUNT CODE AMOUNT
123 BONUS 1,000 INS 500
123 SALARY 5,000 DENTAL 100
123 FLEX 50
My problem is understanding how to display the items in each column starting
at the top.
Thanks for any help.
--
Charles Allen, MVPOn Sep 30, 9:20 am, Charles Allen <cal...@.nospam-bkd.com> wrote:
> I have data in rows that I want to display in columns based on a value in
> each row.
> Here's an example of the data:
> Employee ID Type Code Amount
> 123 PAY BONUS 1,000
> 123 PAY SALARY 5,000
> 123 DED INS 500
> 123 DED DENTAL 100
> 123 DED FLEX 50
> Here's how I'd like to display the data:
> EMPLOYEE CODE AMOUNT CODE AMOUNT
> 123 BONUS 1,000 INS 500
> 123 SALARY 5,000 DENTAL 100
> 123 FLEX 50
> My problem is understanding how to display the items in each column starting
> at the top.
> Thanks for any help.
> --
> Charles Allen, MVP
The best way to produce this type of layout is to create a matrix
report where your pivot column (the column that gets split into
multiple columns based on distinct values) is Type. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||I'll give it a shot. Thanks
--
Charles Allen, MVP
"EMartinez" wrote:
> On Sep 30, 9:20 am, Charles Allen <cal...@.nospam-bkd.com> wrote:
> > I have data in rows that I want to display in columns based on a value in
> > each row.
> >
> > Here's an example of the data:
> > Employee ID Type Code Amount
> > 123 PAY BONUS 1,000
> > 123 PAY SALARY 5,000
> > 123 DED INS 500
> > 123 DED DENTAL 100
> > 123 DED FLEX 50
> >
> > Here's how I'd like to display the data:
> >
> > EMPLOYEE CODE AMOUNT CODE AMOUNT
> > 123 BONUS 1,000 INS 500
> > 123 SALARY 5,000 DENTAL 100
> > 123 FLEX 50
> >
> > My problem is understanding how to display the items in each column starting
> > at the top.
> >
> > Thanks for any help.
> > --
> > Charles Allen, MVP
>
> The best way to produce this type of layout is to create a matrix
> report where your pivot column (the column that gets split into
> multiple columns based on distinct values) is Type. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||Please do suggest me on the below requirement in addition to what Charles
Allen has asked for:
How can I add another column that shows the difference between first CODE
column value and second CODE column value?
Thanks in advance!
"Charles Allen" <callen@.nospam-bkd.com> wrote in message
news:AD136641-F3A8-42B3-A900-92E78174D443@.microsoft.com...
> I'll give it a shot. Thanks
> --
> Charles Allen, MVP
>
> "EMartinez" wrote:
>> On Sep 30, 9:20 am, Charles Allen <cal...@.nospam-bkd.com> wrote:
>> > I have data in rows that I want to display in columns based on a value
>> > in
>> > each row.
>> >
>> > Here's an example of the data:
>> > Employee ID Type Code Amount
>> > 123 PAY BONUS 1,000
>> > 123 PAY SALARY 5,000
>> > 123 DED INS 500
>> > 123 DED DENTAL 100
>> > 123 DED FLEX 50
>> >
>> > Here's how I'd like to display the data:
>> >
>> > EMPLOYEE CODE AMOUNT CODE AMOUNT
>> > 123 BONUS 1,000 INS 500
>> > 123 SALARY 5,000 DENTAL 100
>> > 123 FLEX
>> > 50
>> >
>> > My problem is understanding how to display the items in each column
>> > starting
>> > at the top.
>> >
>> > Thanks for any help.
>> > --
>> > Charles Allen, MVP
>>
>> The best way to produce this type of layout is to create a matrix
>> report where your pivot column (the column that gets split into
>> multiple columns based on distinct values) is Type. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>>|||On Sep 30, 11:20 pm, Charles Allen <cal...@.nospam-bkd.com> wrote:
> I'll give it a shot. Thanks
> --
> Charles Allen, MVP
> "EMartinez" wrote:
> > On Sep 30, 9:20 am, Charles Allen <cal...@.nospam-bkd.com> wrote:
> > > I have data in rows that I want to display in columns based on a value in
> > > each row.
> > > Here's an example of the data:
> > > Employee ID Type Code Amount
> > > 123 PAY BONUS 1,000
> > > 123 PAY SALARY 5,000
> > > 123 DED INS 500
> > > 123 DED DENTAL 100
> > > 123 DED FLEX 50
> > > Here's how I'd like to display the data:
> > > EMPLOYEE CODE AMOUNT CODE AMOUNT
> > > 123 BONUS 1,000 INS 500
> > > 123 SALARY 5,000 DENTAL 100
> > > 123 FLEX 50
> > > My problem is understanding how to display the items in each column starting
> > > at the top.
> > > Thanks for any help.
> > > --
> > > Charles Allen, MVP
> > The best way to produce this type of layout is to create a matrix
> > report where your pivot column (the column that gets split into
> > multiple columns based on distinct values) is Type. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Let me know if I can be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Displaying 2 records per row

I'm looking to display a list of records with 2 per row.
Record1 Record2
Record3 Record4
What's the best way to do this?Use matrix.
Column Grouping expression = RowNumber("matrix1") mod 2
Row Grouping expression = Floor((RowNumber("matrix1") - 1) / 2)
Cell = First(Fields!blabla.Value)
"JBrandt" <JBrandt@.discussions.microsoft.com> wrote in message
news:2C52CAF2-6BEF-4B4C-800D-6DA608BB68F2@.microsoft.com...
> I'm looking to display a list of records with 2 per row.
> Record1 Record2
> Record3 Record4
> What's the best way to do this?|||Can you give more of an example of how to do this these is not quite enough information here
From http://www.developmentnow.com/g/115_2005_5_0_0_516312/Displaying-2-records-per-row.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com

Display Row Data As Cols

I have data in rows that I need to aggregate and display in a columnar fashion and I haven't been able to figure out how to do it. A simplified version of the data as it is stored in the table:

Station Month Day Reading

1 1 1 100

1 1 2 200

1 1 3 300

1 2 1 400

1 2 2 500

And I would like to create a query that returns:

Station Month Day 1 Day 2 Day 3

1 1 100 200 300

1 2 400 500

Any help you can provide or tips to steer me in the right direction are much appreciated.

Dianne Siebold

Are you looking for something like this?

http://msdn2.microsoft.com/en-us/library/ms177410.aspx

|||That is very cool. I'm using SQL Server 2005 but I need to maintain query compatibility with Oracle so I'm looking for something that uses joins or maybe case statements to make it more portable.|||

Then maybe the responses in this thread might be helpful for you

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=438605&SiteID=1

Starting from the second reply by limno.

|||I'm working on using that query logic but I'm just not getting how I can apply it to my query. I'll let you now if I have any success. Thanks!|||I think I got it. I was able to take the concepts from the thread you referred me to and use them in another query. Thanks!

display result in one row

Hi,

I have two tables:

TableA has two columns: Type, ProductID
TableB has two columns: ProductID, ProductName

I need to get all the ProductName for a specific type and this is my SQL:
select TableB.ProductName
from TableA, TableB
where TableA.ProductID = TableB.ProductID
and TableA.Type = 2

The problem with the above SQL is that it returns one column with a few rows, like the following:

ProductA
ProductB
ProductC

Is there a way to have a single SQL that can return the results horizontal like:

ProductA, ProductB, ProductC

Thank you for help in advance.

SnoopyI don't think this is possible only through a single SQL.|||If not, what do you recommend to do? Thanks!|||Can you provide more info?
What DB, are you using a program to run this SQL??
Would it be possible for you load results into a data structure and format accordingly??|||This is called a cross-tab, and it really should be done on the client. They are much better suited to this kind of task.

There are a number of engine-specific ways to do cross-tabs. The problem is that they are engine specific, so one that works on Oracle won't work on DB2 or on SQL Server. We'll leave them out of this discussion for the moment.

There is a portable way to do it, as long as you have no "ties" (in your case that would be duplicate ProductName values) and a small, fixed maximum number of possible cross-tab values. That goes something like:SELECT a.Type
, Min(b1.ProductName)
+ Coalesce(', ' + Min(b2.ProductName), '')
+ Coalesce(', ' + Min(b3.ProductName), '')
FROM TableA AS a
INNER JOIN TableB AS b1
ON (b1.ProductID = a.ProductID)
LEFT OUTER JOIN TableB AS b2
ON (b2.productID = a.ProductID
AND b1.ProductName < b2.ProductName)
LEFT OUTER JOIN TableB AS b3
ON (b3.productID = a.ProductID
AND b2.ProductName < b3.ProductName)
WHERE 2 = a.Type
GROUP BY a.TypeYou can expand this to get more than three values if needed.

-PatP

display records row wise

hello

I am facing a problem in the crystal reports 8.5

In the database table one column is date ,
so here in my report i want to display the records in such a way that
the for every date there should be one colum like for 31 days there should the date in all 31 day

for example please refer the attachment

or see below the requirement is

in my example the records with the days like 21,22
which is diplaying in next to row of the 21 day but i want to show in the same row i.e side by side not in the next row

please any query please pass it so that i can explain you in more detail
urgentplz any one help me|||Maybe you can do this with a crosstab - no idea as I've never used them.
So, although there may be better ways the one I instantly thought of is:

Assuming you are grouping by month:
Create/initialise an array of 31 elements in the group header.
For each detail, add the value into the appropriate array element.
Display each element in its appropriate position in the footer, under its day number.

For completeness you could also suppress any day number heading if > number of days in the month.|||thanks for reply

But i had created with standard report.

but one more issue in this if i take in footer it will display only one record

how can i resolve this|||Don't know what you mean by "But i had created with standard report."

Anyway, if I understand correctly what you really want is for each detail line to be printed but for the 'RT' etc. values to be under the correct day number heading.
Maybe you could left-pad the 'RT' string with a number of spaces dependent on the day number, e.g.

ReplicateString (' ', ({day}-1) * n ) + {table.column}

where n makes the 'RT' text line up underneath the day number heading for any given day number assuming you have n characters between the start of each day number.
(Best to use Courier font or similar, rather than Ariel...)|||Dont use standard report. Use Cross tab option. See help file for more information

Display Number of Records Processed

I've got a stored procedure that processes a TON of records...

What I would liek to do is to write a row to a "progress" table which shows how many rows have actually been processed.

I have a simple counter defined in the procedure:

SET @.COUNTER = 0

Each time the procedure loops through, it increments by 1:

SET @.COUNTER = @.COUNTER + 1

What I would like to do is write rows to a PROCESS table which would reads:

PROCESSED 1000 rows

PROCESSED 2000 rows

PROCESSED ...... rows

etc.

I have a slight idea how to pull this off, but not sure about the whole even number thing by 1000.

If anyone has any insight it would be greatly appreciated!!

Thanks in advance!

Do this:

IF @.COUNTER % 1000 = 0

INSERT .......

|||

Works like a charm!!!

Thanks Tom...

Display Number of Records Processed

I've got a stored procedure that processes a TON of records...

What I would liek to do is to write a row to a "progress" table which shows how many rows have actually been processed.

I have a simple counter defined in the procedure:

SET @.COUNTER = 0

Each time the procedure loops through, it increments by 1:

SET @.COUNTER = @.COUNTER + 1

What I would like to do is write rows to a PROCESS table which would reads:

PROCESSED 1000 rows

PROCESSED 2000 rows

PROCESSED ...... rows

etc.

I have a slight idea how to pull this off, but not sure about the whole even number thing by 1000.

If anyone has any insight it would be greatly appreciated!!

Thanks in advance!

Do this:

IF @.COUNTER % 1000 = 0

INSERT .......

|||

Works like a charm!!!

Thanks Tom...

Display details section for each group

Hi,

I want to display details ection for each group in a single table using one dataset.

For ex:

Group 1 Row --> Name Age

Details 1 Row--> XXX 30

Details 2 Row--> YYY 20

Group 2 Row --> City Country

Details 1 Row--> CCC ZZZZ

Details 2 Row--> BBB AAAA

Hi,

Read this article in BOL please: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/a4aa29f9-eaa4-4116-8e22-87748c5ef0b8.htm

It is much more easier tham I write here

Regards,

Janos

|||

Hi Janos,

Thanks for the reply. But i am not able to access the url given by you. Can you please provide me the correct URL?

Thanks in Advance.,

Parimal

|||

Hi,

it is in the books online for SQL server 2005. Try to copy/paste into it, or open it on the MS website at here: http://technet.microsoft.com/en-us/library/ms159169.aspx

Regards,

Janos

display data without auto grouping in report builder

I have a report built by report builder. When I run report several column
data did not show repeated data on each row. Instead it just show one time
and the other repeated data field just auto merged with the first cell. Is
there any way to show each row data no matter it is the same as the one in
the cell above?
Example now I see:
Type City State
HSLA PHILADELPHIA PA
NORCROSS GA
PAOLI PA
PAOLI PA
What I want to get is:
HSLA PHILADELPHIA PA
HSLA NORCROSS GA
HSLA PAOLI PA
HSLA PAOLI PA
Is it possible to do that in "design report" or after hit "Run Report"? I
don't want the auto grouping in adhoc report. Please HELP! I really
appreciate it.I figured it out. The column is shown in the group if you click 'sort and
group'. Just drag all the fields you want and drop them on the table so that
the group is created in entity name instead of the individual field names.
"Daisy" wrote:
> I have a report built by report builder. When I run report several column
> data did not show repeated data on each row. Instead it just show one time
> and the other repeated data field just auto merged with the first cell. Is
> there any way to show each row data no matter it is the same as the one in
> the cell above?
> Example now I see:
> Type City State
> HSLA PHILADELPHIA PA
> NORCROSS GA
> PAOLI PA
> PAOLI PA
> What I want to get is:
> HSLA PHILADELPHIA PA
> HSLA NORCROSS GA
> HSLA PAOLI PA
> HSLA PAOLI PA
> Is it possible to do that in "design report" or after hit "Run Report"? I
> don't want the auto grouping in adhoc report. Please HELP! I really
> appreciate it.
>