Data Grouping

Question

Data Grouping

aanryn

Old Hand

Points: 355
More actions
January 30, 2008 at 4:48 am

#186080

Hi everyone,
I am an novice in Sql. I have tried using group by, but I'm not able to get the desired output. my table is here say it as "Emp"
eno eid
1 1
2 2
3 3
4 3
5 3
6 4
7 4
8 4
9 4
10 5
11 5
12 5
I want the output as
eno eid
3 3
4 3
6 4
7 4
10 5
11 5
Can any one help me on this.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

No, not unless you give us some more information. Please post DLL in the form of CREATE TABLE scripts, sample data in the form of INSERT scripts, a brief summary of what you are trying to achieve, expected results, and what you have tried so far.

Thanks

John

aanryn Old Hand Points: 355 More actions · Answer 2

Hi,

I have created a table

CREATE TABLE emp(eno int identity(1,1), eid int)

Inserted values

INSERT INTO emp VALUES (1)

INSERT INTO emp VALUES (2)

INSERT INTO emp VALUES (3)

INSERT INTO emp VALUES (4)

INSERT INTO emp VALUES (5)

What i have tried

select max(eid) from emp group by eid having count(eid)>1

not getting desired output.

My result should be like this

eno eid

----------- -----------

3 3

4 3

6 4

7 4

10 5

11 5

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

I'm sorry, but I still can't work out what you're trying to do. Please will you describe in words what your query should achieve?

Thanks

John

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 4

Sorry there is no clarity in the post. What is the result you want to achieve? Please Post clearly.

aanryn Old Hand Points: 355 More actions · Answer 5

I have two tables eno and eid in my emp table. I have inserted some values where eno is a primary key. So, the table looks like this

eno eid

---- ----

1 1

2 1

3 2

4 2

5 2

6 3

7 3

8 3

9 4

10 4

In eid column 1,2,3,4 is repeated several times.

I want from eid where data is 2 and 3 because it is repeated 3 times. I just want two rows from eno column either 3 or 4, 4 or 5, 3 or 5 and similarly for eid column with 3 i want 6 or 7, 7 or 8, 6 or 8. I just want only two times repetition of 2 and 3 in my result table

My result should look like

eno eid

---- ----

3 2

4 2

6 3

8 3

Could you please help me out.

ChiragNS One Orange Chip Points: 26137 More actions · Answer 6

ChiragNS

One Orange Chip

Points: 26137

February 1, 2008 at 3:01 am

#774109

Sorry still not clear...

"Keep Trying"

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

I fully realize that this thread is 3-1/2 years old and the OP probably got a solution a very long time ago. I found this thread totally by accident while I was looking for something else. Since it was never answered, I though I'd take the time to provide a solution in case someone else might be looking for something similar.

This is simply a "Select top 2 from each group" problem with the added caveat that the groups we're selecting from must have at least 3 items. Here's the solution with some test data. As is normal, the details are in the comments in the code...

--=============================================================================

-- Create and populate a test table using the data from the original post.

-- Nothing in this section is a part of the solution to the problem.

-- We're just building test data here.

--=============================================================================

--===== Conditionally drop the test table to make reruns in SSMS easier

IF OBJECT_ID('TempDB..#MyHead','U') IS NOT NULL

DROP TABLE #MyHead

;

--===== Create the test table (no indexes included for this test)

CREATE TABLE #MyHead

(

eno INT,

eid INT

)

;

--===== Populate the test table with data from the original post

INSERT INTO #MyHead

(eno, eid)

SELECT 1,1 UNION ALL

SELECT 2,2 UNION ALL

SELECT 3,3 UNION ALL

SELECT 4,3 UNION ALL

SELECT 5,3 UNION ALL

SELECT 6,4 UNION ALL

SELECT 7,4 UNION ALL

SELECT 8,4 UNION ALL

SELECT 9,4 UNION ALL

SELECT 10,5 UNION ALL

SELECT 11,5 UNION ALL

SELECT 12,5

;

--=============================================================================

-- One possible solution to the problem.

--=============================================================================

--===== Return the top 2 from each group having at least 3 rows in the group

SELECT mh1.*

FROM #MyHead mh1

WHERE eno IN ( --=== Find the eno's for only two rows for each eid

SELECT TOP 2

mh2.eno

FROM #MyHead mh2

WHERE mh2.eid = mh1.eid

)

AND mh1.eid IN ( --==== Find only those eid's that have at least 3 rows

SELECT eid

FROM #MyHead

GROUP BY eid

HAVING COUNT(*) >= 3

)

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 8

How about this Jeff ???

; WITH cte AS

(

SELECT *

,RowNum = ROW_NUMBER() OVER( PARTITION BY eid ORDER BY eno )

,GrpCnt = COUNT(*) OVER( PARTITION BY eid )

FROM emp

)

SELECT eno , eid

FROM cte

WHERE RowNum <= 2 AND GrpCnt > 2

SQL_By_Chance Hall of Fame Points: 3915 More actions · Answer 9

I think in the given scenario Jeff's solution with CTE is best possible.

But in case you want to dwell in subqueries. Can you please post the result that you expect.

Regards,

Ankit

______________________________________________________________________

Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 10

SQL_By_Chance (10/2/2011)
I think in the given scenario Jeff's solution with CTE is best possible.
But in case you want to dwell in subqueries. Can you please post the result that you expect.
Regards,
Ankit

Ankit... look at the original post... the "result that you expect" is already there. Besides, this post is over 3 years old... the OP might not even have the same email address anymore and probably wouldn't respond if it was. 😉

Also, I'd love to see your rendition of it as CTE's... I just don't have the time to come up with multiple answers anymore. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 11

ColdCoffee (10/1/2011)
How about this Jeff ???

That's some sharp code, CC. Well done. However, shorter code using "new" stuff like ROW_NUMBER() isn't always better. Please consider the following...

--=============================================================================

-- Create and populate a test table using the data from the original post.

-- Nothing in this section is a part of the solution to the problem.

-- We're just building test data here.

--=============================================================================

--===== Conditionally drop the test table to make reruns in SSMS easier

IF OBJECT_ID('TempDB..#MyHead','U') IS NOT NULL

DROP TABLE #MyHead

;

--===== Create the test table (no indexes included for this test)

CREATE TABLE #MyHead

(

eno INT IDENTITY(1,1),

eid INT

)

;

--===== Populate the test table with data from the original post

-- INSERT INTO #MyHead

-- (eno, eid)

-- SELECT 1,1 UNION ALL

-- SELECT 2,2 UNION ALL

-- SELECT 3,3 UNION ALL

-- SELECT 4,3 UNION ALL

-- SELECT 5,3 UNION ALL

-- SELECT 6,4 UNION ALL

-- SELECT 7,4 UNION ALL

-- SELECT 8,4 UNION ALL

-- SELECT 9,4 UNION ALL

-- SELECT 10,5 UNION ALL

-- SELECT 11,5 UNION ALL

-- SELECT 12,5

--;

--=============================================================================

-- Create and populate a million row test table.

-- Nothing in this section is a part of the solution to the problem.

-- We're just building test data here.

--=============================================================================

--===== Conditionally drop the test table to make reruns in SSMS easier

IF OBJECT_ID('TempDB..#MyHead','U') IS NOT NULL

DROP TABLE #MyHead

;

SELECT TOP 1000000

eno = IDENTITY(INT,1,1),

eid = ABS(CHECKSUM(NEWID()))%500000+1

INTO #MyHead

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

;

ALTER TABLE #MyHead

ADD PRIMARY KEY CLUSTERED (eno)

;

CREATE INDEX IX_#MyHead_eid

ON #MyHead (eid)

;

GO

--=============================================================================

-- Demonstrate both answers

--=============================================================================

set statistics time,io on;

--===== Return the top 2 from each group having at least 3 rows in the group

SELECT mh1.*

FROM #MyHead mh1

WHERE eno IN ( --=== Find the eno's for only two rows for each eid

SELECT TOP 2

mh2.eno

FROM #MyHead mh2

WHERE mh2.eid = mh1.eid

)

AND mh1.eid IN ( --==== Find only those eid's that have at least 3 rows

SELECT eid

FROM #MyHead

GROUP BY eid

HAVING COUNT(*) >= 3

)

;

PRINT REPLICATE('=',100)

;

WITH cte AS

(

SELECT *

,RowNum = ROW_NUMBER() OVER( PARTITION BY eid ORDER BY eno )

,GrpCnt = COUNT(*) OVER( PARTITION BY eid )

FROM #MyHead

)

SELECT eno , eid

FROM cte

WHERE RowNum <= 2 AND GrpCnt > 2

;

set statistics time,io off;

Here're the results from my old but trusty desktop...

SQL Server parse and compile time:

CPU time = 47 ms, elapsed time = 72 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

(323324 row(s) affected)

Table '#MyHead_____________________________________________________________________________________________________________000000000011'.

Scan count 594247, logical reads 1899486, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 8656 ms, elapsed time = 29538 ms.

====================================================================================================

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

(323324 row(s) affected)

Table 'Worktable'. Scan count 3, logical reads 3729057, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#MyHead_____________________________________________________________________________________________________________000000000011'.

Scan count 1, logical reads 1738, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 23062 ms, elapsed time = 49125 ms.

Heh... and I'm sure that someone can beat us both. 😀 I just haven't had enough coffee this morning to say one way or another.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)