Post reply

Composite Primary Key Help...Query

  • 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 🙁

  • December 17, 2013 at 8:19 am

    #1674092

    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!

  • December 17, 2013 at 8:46 am

    #1674108

    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

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

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