Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Data Grouping Expand / Collapse
Author
Message
Posted Wednesday, January 30, 2008 4:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 27, 2009 3:32 AM
Points: 21, Visits: 55
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.
Post #449278
Posted Wednesday, January 30, 2008 4:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 5,490, Visits: 10,347
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
Post #449282
Posted Wednesday, January 30, 2008 5:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 27, 2009 3:32 AM
Points: 21, Visits: 55
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 (3)
INSERT INTO emp VALUES (3)
INSERT INTO emp VALUES (4)
INSERT INTO emp VALUES (4)
INSERT INTO emp VALUES (4)
INSERT INTO emp VALUES (4)
INSERT INTO emp VALUES (5)
INSERT INTO emp VALUES (5)
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
Post #449295
Posted Wednesday, January 30, 2008 5:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 5,490, Visits: 10,347
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
Post #449299
Posted Wednesday, January 30, 2008 5:58 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 7:16 AM
Points: 3,931, Visits: 1,102
Sorry there is no clarity in the post. What is the result you want to achieve? Please Post clearly.



Post #449302
Posted Friday, February 1, 2008 2:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 27, 2009 3:32 AM
Points: 21, Visits: 55
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.


Post #450370
Posted Friday, February 1, 2008 3:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:08 AM
Points: 2,365, Visits: 1,846
Sorry still not clear...



"Keep Trying"
Post #450376
Posted Saturday, October 1, 2011 1:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 35,772, Visits: 32,445
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1184096
Posted Saturday, October 1, 2011 9:07 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:03 PM
Points: 2,262, Visits: 5,427
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

Post #1184132
Posted Sunday, October 2, 2011 11:19 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:05 AM
Points: 122, Visits: 292
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.”
Post #1184265
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse