Composite Primary Key Help...Query

Question

Composite Primary Key Help...Query

learning_sql

Right there with Babe

Points: 727
More actions
December 17, 2013 at 8:10 am

#395999

Hi,
I have been tasked with editing an Ingres DB Table. I am required to search for specific records based upon a date range (easy enough as there are columns containing from_date and to_date). Then I will duplicate that record and amend certain columns. The table contains a composite primary key - 2 columns, a reference and a foreign_reference. The reference is an integer ranging from 5000000 upwards usually. The foreign_reference is an integer to uniquely identify each row:
Table 1
Reference, foreign_Reference
5000000, 1
5000000, 2
5000000, 3
5000001, 1
5000001, 2
I am hoping to select (for use with my insert statements) the highest foreign_reference for each reference?
I have been trying a whole range of things (first/top/max) without success. I realise a sub-select or a select join onto the table is the way forward but I would like some help please?
I have another problem to deal with once this has been solved 🙁

Viewing 3 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

well answers to questions for an Ingres database will hopefully conform closely with TSQL answers you get here.

the question is not really clear to me; when you run your max query, which i assume it looks something like below, what information is lacking for you?

what data in the subquery do you think you need?

/*

--Results

Reference Minforeign_reference Maxforeign_reference

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

5000000 1 3

5000001 1 2

*/

;WITH [Table1]([Reference],[foreign_Reference])

AS

(

SELECT '5000000','1' UNION ALL

SELECT '5000000','2' UNION ALL

SELECT '5000000','3' UNION ALL

SELECT '5000001','1' UNION ALL

SELECT '5000001','2'

)

SELECT

Reference,

MIN(foreign_reference) As Minforeign_reference,

MAX(foreign_reference) As Maxforeign_reference

FROM [Table1]

GROUP BY Reference

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

learning_sql Right there with Babe Points: 727 More actions · Answer 2

Hilariously you solved it for me....

I had made a very rookie mistake and simply needed to look at yours to solve it. Too embarrassed to state what it was....for anyone curious of the answer simply copy the Gentleman and a Scholar above