find the greater element in list

I have a table with 2 field Id and amount.

I want to select the maximum amount for all the Ids.

e.g.

IdAmount

1

23

1

47

2

23

250

37

The result set should be

1

47

2

50

3

7

Can any one give any sql query to

fetch the appropriate result?

select ID, max(Amount)

from your table

Group by id

|||

Did it work?

If so, can you mark answer as correct. thanks

sql

find out if an ID is tied to multiple ids in another table

Table 1

stock

stock_id

cat_id

stock_name

is_fund

Table 2

Fund_contents

fund_id

stock_id

I need to find out all the stock_id's from the stock table that are associated with 2 different fund_id's in the second table and the stock_id's in the first table have to have is_fund = 0 from the stock table

any ideas?

How about,

SELECT S.Stock_Id
FROM StockAs S
JOIN Fund_Contents F
ON S.Stock_Id = F.Stock_Id
WHERE S.IS_Fund = 0
GROUP BY S.Stock_Id
HAVINGCOUNT(F.Fund_Id) >= 2

Hope this helps,

|||

SELECT S.Stock_Id
FROM StockAs S
JOIN Fund_Contents F
ON S.Stock_Id = F.Stock_Id

WHERE S.IS_Fund = 0
GROUP BY S.Stock_Id
HAVINGCOUNT(F.Fund_Id) >= 2

|||

perfect thanks both of you

find matching sets of rows

Given an ID (column B), I need to find which IDs have identical data.

That is, given '200', I want the desired result to be:
100

The idea is that the system sees that id=200 has 5 records with the
indicated data in cols C and D.

It should then find any other ids with the exact same data for those
columns.

Note, in this case, both 200 and 100 have (30:1, 30:2, 30:3, 40:4,
40:5) so they match. 300 and 400 should NOT be returned.

Any bright ideas out there? Thanks!

DECLARE @.a TABLE(A int, B int, C int, D int)
DECLARE @.b TABLE(A int, B int, C int, D int)

INSERT INTO @.a (A, B, C, D) VALUES (1, 100, 30, 1)
INSERT INTO @.a (A, B, C, D) VALUES (2, 100, 30, 2)
INSERT INTO @.a (A, B, C, D) VALUES (3, 100, 30, 3)
INSERT INTO @.a (A, B, C, D) VALUES (4, 100, 40, 4)
INSERT INTO @.a (A, B, C, D) VALUES (5, 100, 40, 5)

INSERT INTO @.a (A, B, C, D) VALUES (6, 200, 30, 1)
INSERT INTO @.a (A, B, C, D) VALUES (7, 200, 30, 2)
INSERT INTO @.a (A, B, C, D) VALUES (8, 200, 30, 3)
INSERT INTO @.a (A, B, C, D) VALUES (9, 200, 40, 4)
INSERT INTO @.a (A, B, C, D) VALUES (10, 200, 40, 5)

INSERT INTO @.a (A, B, C, D) VALUES (11, 300, 30, 1)
INSERT INTO @.a (A, B, C, D) VALUES (12, 300, 30, 2)
INSERT INTO @.a (A, B, C, D) VALUES (13, 300, 40, 3)
INSERT INTO @.a (A, B, C, D) VALUES (14, 400, 40, 4)
INSERT INTO @.a (A, B, C, D) VALUES (15, 400, 40, 5)

SELECT * FROM @.afigital wrote:
> Given an ID (column B), I need to find which IDs have identical data.
> That is, given '200', I want the desired result to be:
> 100
> The idea is that the system sees that id=200 has 5 records with the
> indicated data in cols C and D.
> It should then find any other ids with the exact same data for those
> columns.
> Note, in this case, both 200 and 100 have (30:1, 30:2, 30:3, 40:4,
> 40:5) so they match. 300 and 400 should NOT be returned.
> Any bright ideas out there? Thanks!
>
> DECLARE @.a TABLE(A int, B int, C int, D int)
> DECLARE @.b TABLE(A int, B int, C int, D int)
> INSERT INTO @.a (A, B, C, D) VALUES (1, 100, 30, 1)
> INSERT INTO @.a (A, B, C, D) VALUES (2, 100, 30, 2)
> INSERT INTO @.a (A, B, C, D) VALUES (3, 100, 30, 3)
> INSERT INTO @.a (A, B, C, D) VALUES (4, 100, 40, 4)
> INSERT INTO @.a (A, B, C, D) VALUES (5, 100, 40, 5)
> INSERT INTO @.a (A, B, C, D) VALUES (6, 200, 30, 1)
> INSERT INTO @.a (A, B, C, D) VALUES (7, 200, 30, 2)
> INSERT INTO @.a (A, B, C, D) VALUES (8, 200, 30, 3)
> INSERT INTO @.a (A, B, C, D) VALUES (9, 200, 40, 4)
> INSERT INTO @.a (A, B, C, D) VALUES (10, 200, 40, 5)
> INSERT INTO @.a (A, B, C, D) VALUES (11, 300, 30, 1)
> INSERT INTO @.a (A, B, C, D) VALUES (12, 300, 30, 2)
> INSERT INTO @.a (A, B, C, D) VALUES (13, 300, 40, 3)
> INSERT INTO @.a (A, B, C, D) VALUES (14, 400, 40, 4)
> INSERT INTO @.a (A, B, C, D) VALUES (15, 400, 40, 5)
> SELECT * FROM @.a

Thanks for posting the sample data. It really does help however if you
include KEYS with your DDL. Your table doesn't seem to have a key - all
the columns are nullable. That may make your problem a lot harder to
solve.

Assuming you can rewrite the table variable as:

DECLARE @.a TABLE(A int, B int, C int, D int, PRIMARY KEY (b,c,d));

Then you can do:

DECLARE @.i INT ;
SET @.i = 100 ;

SELECT B.b
FROM @.a AS A
JOIN @.a AS B
ON A.b = @.i
AND A.c = B.c
AND A.d = B.d
AND B.b <> @.i
GROUP BY B.b
HAVING COUNT(*)=
(SELECT COUNT(*)
FROM @.a
WHERE b = @.i);

If I'm wrong and you don't have such a key then it's not clear how you
want to handle duplicates. Here's a different example, assuming that A
is the key and that duplicates are significant, i.e. you want the same
number of rows in each set identified by column B:

SELECT B.b
FROM @.a AS A
JOIN @.a AS B
ON A.b = @.i
AND A.c = B.c
AND A.d = B.d
AND B.b <> @.i
GROUP BY B.b
HAVING COUNT(DISTINCT A.a)=
(SELECT COUNT(DISTINCT a)
FROM @.a
WHERE b = @.i);

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Try:

SELECT b.B
FROM @.a a
join @.a b on b.C = a.C
and b.D = a.D
where a.B = 200
and b.B <> 200
group by
b.B
having
count (*) = (select count (*) from @.a where B = 200)

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"figital" <mharen@.gmail.com> wrote in message
news:1144788286.896210.141080@.i40g2000cwc.googlegr oups.com...
Given an ID (column B), I need to find which IDs have identical data.

That is, given '200', I want the desired result to be:
100

The idea is that the system sees that id=200 has 5 records with the
indicated data in cols C and D.

It should then find any other ids with the exact same data for those
columns.

Note, in this case, both 200 and 100 have (30:1, 30:2, 30:3, 40:4,
40:5) so they match. 300 and 400 should NOT be returned.

Any bright ideas out there? Thanks!

DECLARE @.a TABLE(A int, B int, C int, D int)
DECLARE @.b TABLE(A int, B int, C int, D int)

INSERT INTO @.a (A, B, C, D) VALUES (1, 100, 30, 1)
INSERT INTO @.a (A, B, C, D) VALUES (2, 100, 30, 2)
INSERT INTO @.a (A, B, C, D) VALUES (3, 100, 30, 3)
INSERT INTO @.a (A, B, C, D) VALUES (4, 100, 40, 4)
INSERT INTO @.a (A, B, C, D) VALUES (5, 100, 40, 5)

INSERT INTO @.a (A, B, C, D) VALUES (6, 200, 30, 1)
INSERT INTO @.a (A, B, C, D) VALUES (7, 200, 30, 2)
INSERT INTO @.a (A, B, C, D) VALUES (8, 200, 30, 3)
INSERT INTO @.a (A, B, C, D) VALUES (9, 200, 40, 4)
INSERT INTO @.a (A, B, C, D) VALUES (10, 200, 40, 5)

INSERT INTO @.a (A, B, C, D) VALUES (11, 300, 30, 1)
INSERT INTO @.a (A, B, C, D) VALUES (12, 300, 30, 2)
INSERT INTO @.a (A, B, C, D) VALUES (13, 300, 40, 3)
INSERT INTO @.a (A, B, C, D) VALUES (14, 400, 40, 4)
INSERT INTO @.a (A, B, C, D) VALUES (15, 400, 40, 5)

SELECT * FROM @.a|||The key is Column A (ident). Sorry for not providing more complete ddl.
I will check out these suggestions, thanks!|||DECLARE @.a TABLE(A int IDENTITY(1,1) PRIMARY KEY, B int, C int, D int);

INSERT INTO @.a (B, C, D) VALUES (100, 30, 1)
INSERT INTO @.a (B, C, D) VALUES (100, 30, 2)
INSERT INTO @.a (B, C, D) VALUES (100, 30, 3)
INSERT INTO @.a (B, C, D) VALUES (100, 40, 4)
INSERT INTO @.a (B, C, D) VALUES (100, 40, 5)
INSERT INTO @.a (B, C, D) VALUES (200, 30, 1)
INSERT INTO @.a (B, C, D) VALUES (200, 30, 2)
INSERT INTO @.a (B, C, D) VALUES (200, 30, 3)
INSERT INTO @.a (B, C, D) VALUES (200, 40, 4)
INSERT INTO @.a (B, C, D) VALUES (200, 40, 5)
INSERT INTO @.a (B, C, D) VALUES (300, 30, 1)
INSERT INTO @.a (B, C, D) VALUES (300, 30, 2)
INSERT INTO @.a (B, C, D) VALUES (300, 40, 3)
INSERT INTO @.a (B, C, D) VALUES (400, 40, 4)
INSERT INTO @.a (B, C, D) VALUES (400, 40, 5)
INSERT INTO @.a (B, C, D) VALUES (500, 30, 1)
INSERT INTO @.a (B, C, D) VALUES (500, 30, 2)
INSERT INTO @.a (B, C, D) VALUES (500, 30, 3)
INSERT INTO @.a (B, C, D) VALUES (500, 40, 4)
INSERT INTO @.a (B, C, D) VALUES (500, 40, 5)
INSERT INTO @.a (B, C, D) VALUES (500, 31, 6)

--SELECT * FROM @.a

DECLARE @.i INT ;
SET @.i = 200 ;

-- solution

The above solutions return 500 even though it contains _6_ records and
200 contains 5 records.|||Our solutions fall into the category of "Relational Division". In both
solutions, we allow for a remainder. What you want is exact division.
Here's a solution for exact division:

SELECT a.B
FROM @.a a
left
join @.a b on b.C = a.C
and b.D = a.D
and b.B = 200
where a.B <> 200
group by
a.B
having
count (distinct a.D) = (select count (distinct D) from @.a where B = 200)

If A is an identity, you could use count (distinct A) where applicable.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"figital" <mharen@.gmail.com> wrote in message
news:1144792950.602964.118760@.g10g2000cwb.googlegr oups.com...
DECLARE @.a TABLE(A int IDENTITY(1,1) PRIMARY KEY, B int, C int, D int);

INSERT INTO @.a (B, C, D) VALUES (100, 30, 1)
INSERT INTO @.a (B, C, D) VALUES (100, 30, 2)
INSERT INTO @.a (B, C, D) VALUES (100, 30, 3)
INSERT INTO @.a (B, C, D) VALUES (100, 40, 4)
INSERT INTO @.a (B, C, D) VALUES (100, 40, 5)
INSERT INTO @.a (B, C, D) VALUES (200, 30, 1)
INSERT INTO @.a (B, C, D) VALUES (200, 30, 2)
INSERT INTO @.a (B, C, D) VALUES (200, 30, 3)
INSERT INTO @.a (B, C, D) VALUES (200, 40, 4)
INSERT INTO @.a (B, C, D) VALUES (200, 40, 5)
INSERT INTO @.a (B, C, D) VALUES (300, 30, 1)
INSERT INTO @.a (B, C, D) VALUES (300, 30, 2)
INSERT INTO @.a (B, C, D) VALUES (300, 40, 3)
INSERT INTO @.a (B, C, D) VALUES (400, 40, 4)
INSERT INTO @.a (B, C, D) VALUES (400, 40, 5)
INSERT INTO @.a (B, C, D) VALUES (500, 30, 1)
INSERT INTO @.a (B, C, D) VALUES (500, 30, 2)
INSERT INTO @.a (B, C, D) VALUES (500, 30, 3)
INSERT INTO @.a (B, C, D) VALUES (500, 40, 4)
INSERT INTO @.a (B, C, D) VALUES (500, 40, 5)
INSERT INTO @.a (B, C, D) VALUES (500, 31, 6)

--SELECT * FROM @.a

DECLARE @.i INT ;
SET @.i = 200 ;

-- solution

The above solutions return 500 even though it contains _6_ records and
200 contains 5 records.|||Tom and David,

Thank you very much for your help!

I had to add a check because of the left join but otherwise, awesome!

SELECT a.B
FROM @.a a
left join @.a b on b.C = a.C
and b.D = a.D
and b.B = @.i
where a.B <> @.i and NOT (B.C IS NULL OR B.D IS NULL)
group by
a.B
having
count (distinct a.D) = (select count (distinct D) from @.a where B =
@.i)|||Oh, OK. The input data didn't have nulls, so I didn't go there. Glad you
now have a solution. :-)

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"figital" <mharen@.gmail.com> wrote in message
news:1144797933.088430.155480@.g10g2000cwb.googlegr oups.com...
Tom and David,

Thank you very much for your help!

I had to add a check because of the left join but otherwise, awesome!

SELECT a.B
FROM @.a a
left join @.a b on b.C = a.C
and b.D = a.D
and b.B = @.i
where a.B <> @.i and NOT (B.C IS NULL OR B.D IS NULL)
group by
a.B
having
count (distinct a.D) = (select count (distinct D) from @.a where B =
@.i)

find all the employees under one manager (was "Need Help with Query")

I have an employee table with manager id and employee ids , i need to find all the employee ids for a manager id . Each employee can be a manager in turn . So I need to find all the employees under one manager and if any of the employee is in turn a manager , i need to find the employees under him as well .

The table structure is defined and i cannot edit it .

Please let me know if we could have a single query to do this .

Thank you
kishoreYou don't state what version of SQL Server you are using. New to SQL Server 2005 is Common Table Expressions (CTEs). An article on MSDN describes exactly what you are looking for: Recursive Queries Using Common Table Expressions (http://msdn2.microsoft.com/en-us/library/ms186243.aspx).

I'm afraid this is somewhat more tricky on SQL Server 2000.|||I need to support SQL server 2000 as well as 2005 ,also other Database like oracle , Db2 and sybase. Apart from Common Table Expression , is there any way i can do it .|||Well, the code won't be identical on the various platforms anyway. CTEs are supported on SQL Server 2005 and DB2, and perhaps the latest version of Sybase as well. It is a part of the SQL Standard since SQL:1999, so I would suggest using it where possible. For the rest you could create a temporary table, and use pretty much the same idea as in the CTE:

1. Insert all without a parent (top level)
2. Insert all whose having those in 1 as parent
3. Recursively insert all whose having their parent inserted, but are not inserted themselves yet.

I know, it's not very pretty code, but it works. Hope you got my idea :)|||SELECT e.known_as_and_surname
AS 'Employee'
,m.known_as_and_surname
AS 'Manager'
FROM employee AS e
LEFT OUTER JOIN employee AS m
ON m.employee_number = e.manager_number

Any good to you?
Self-join genius courtesy of Rudy at www.r937.com :beer:|||Any good to you?
That would be nice for 2 levels (manager and employee).
Triumph wants a top person's subordinates from 1, 2, x levels down.

If there is a fixed number of levels you can union that number (minus one) of select statements. If not, the temporary table that roac describes would be useful.

<edit>
Congratulations!|||roac approach would work i guess , but i was looking for a solution where in a single query would do the trick , but i guess its not going to be the case . I needed this solution to improve performance .|||no single query is ever going to work the same in sql server 2000, sql server 2005, oracle, db2, and sybase

if you are writing an application that "abstracts" the database layer, you are never going to achieve your result by trying to abstract the sql

instead, you need to abstract the information request, and write specific sql modules for each database -- in this case, CONNECT BY for oracle, CTEs for those that support it (it's part of the sql standard), a recursive call for other databases, etc.|||Thanks to all for helping me. I think i will do the recurvise calls using Java .|||Well good luck!

I'd love to see a solution to this if/when you get one ;)|||Thanks to all for helping me. I think i will do the recurvise calls using Java .
Ugh.
You would get excellent performance using the algorithm suggested by ROAC. I'd bet it would beat any recursive algorithm hands down. Recursion requires a separate call for each item in the hierarchy, while ROAC's suggestion only requires one call for each level in the hierarchy.|||I noticed the thread changed title... This simple query solves it as the title asks ;)
To find all the employees under ONE manager:

SELECT e.known_as_and_surname AS 'Employee'
FROM employee AS e
LEFT OUTER JOIN employee AS m
ON m.employee_number = e.manager_number
WHERE m.known_as_and_surname = '<InsertNameOfManager>'|||Without editing the layout of the table you're going to have difficulty getting a work around. If you know the max number of levels then you could hack it with a single query (based on rudy's example) :

SELECT
e1.name AS 'Lvl1',
e2.name AS 'Lvl2',
e3.name AS 'Lvl3',
e4.name AS 'Lvl4',
e5.name AS 'Lvl5',
FROM employee AS e1
LEFT OUTER JOIN employee AS e2 ON e2.employee_number = e1.manager_number
LEFT OUTER JOIN employee AS e3 ON e3.employee_number = e2.manager_number
LEFT OUTER JOIN employee AS e4 ON e4.employee_number = e3.manager_number
LEFT OUTER JOIN employee AS e5 ON e5.employee_number = e4.manager_number|||Queston : does MSSQL (2000) support recursive procedures?|||even if there is no maximum number of levels, you could still use the 4-way self-join to show just 4 levels of subtree, with links at each bottom level node if it has any further levels below it

and of course clicking on one of the links returns up 4 lower levels below that node, so in effect it "recurses" down the tree but it is the user driving the process 4 levels at a time

usually, any tree that is large enough to have "unlimited" levels is at the same time also too large to allow the complete display of the entire tree, so recursion would be neither practical nor desirable anyway

the way i see it ;)|||Rudy you have a point, however if this is for report generation and there are a lot of employees/managers then full recursion is going to be needed. Or you're planning on making a nice big tree heirarchy using some fancy graphical functionality then i suspect returning the full set will still be beneficial.