Professional Web Applications Themes

SELECT "newest Version". Please help :) - Microsoft SQL / MS SQL Server

Hi, Sorry for the crappy Subject, it's kinda hard for me to "catch" my problem in a snappy "Subject". I have a database containing several versions and languages of pdfs for several products. I want to make a "select" which delivers a table of products with only _1_ pdf per product. It should be the "newest" version in a selected language (or english language if the selected is not available) This is my (simplified) Table: TABLE ~~~~~ FK_Product (foreign key for the product this pdf is for) FK_PDF (foreign key for the pdf) Version (Version number of this pdf, the ...

  1. #1

    Default SELECT "newest Version". Please help :)

    Hi,

    Sorry for the crappy Subject, it's kinda hard for me
    to "catch" my problem in a snappy "Subject".

    I have a database containing several versions and
    languages of pdfs for several products. I want to make
    a "select" which delivers a table of products with only
    _1_ pdf per product. It should be the "newest" version in
    a selected language (or english language if the selected
    is not available)

    This is my (simplified) Table:

    TABLE
    ~~~~~
    FK_Product (foreign key for the product this pdf is for)
    FK_PDF (foreign key for the pdf)
    Version (Version number of this pdf, the higher the newer)
    Language (language key. 0=englisch(default), 1=german...)

    INPUT:
    ~~~~~~
    Desired_Language

    OUTPUT
    ~~~~~~
    Select should return a table with 1 entry per product,
    containing the newest version of the pdfs available in
    desired_language. If desired_language is not available
    for the newest version, the newest version in english
    (language=0) (NOT the newest of desired languag! Priority
    on Version.)

    I've been trying for some hours now, cannot get this
    together by myself :(

    Thanks for any input!

    - Oliver


    Oliver Guest

  2. #2

    Default Re: SELECT "newest Version". Please help :)

    Hi Oliver,

    Try the following (untested):

    DECLARE language int

    SET language = 1

    SELECT FK_Product, FK_PDF, Version, Language
    FROM MyTable t
    INNER JOIN
    (SELECT FK_Product, Version, MAX(Language) AS max_language
    FROM MyTable
    WHERE language = language OR language = 0
    GROUP BY FK_Product, Version) as lan
    ON t.FK_Product = lan.FK_Product
    AND t.Version = lan.Version
    AND t.Language = lan.max_language
    INNER JOIN
    (SELECT FK_Product, MAX(Version) AS max_version
    FROM MyTable
    GROUP BY FK_Product) AS ver
    ON lan.FK_Product = ver.FK_Product
    AND lan.Version = ver.max_version


    If this doesn't provided the expected results, please post table creation
    scripts, sample data insert scripts and expected results as described in
    www.aspfaq.com/5006




    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Oliver Vaross" <org> wrote in message
    news:02ba01c3656b$1832bbf0$gbl... 


    Jacco Guest

  3. #3

    Default Re: SELECT "newest Version". Please help :)

    Hi Jacco,
     

    Great, this helped very much! I did not know, that I
    can "JOIN" Subselects. This is great stuff :)

    Just had to change one line to make you untested proc
    working:

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

    DECLARE language int

    SET language = 1

    SELECT t.FK_Product, t.FK_PDF, t.Version, t.Language
    FROM MyTable as t

    INNER JOIN ( SELECT FK_Product, Version, MAX(Language)
    AS max_language
    FROM MyTable
    WHERE language = language OR language =
    0
    GROUP BY FK_Product, Version
    ) as lan ON t.FK_Product = lan.FK_Product AND
    t.Version = lan.Version AND t.Language = lan.max_language

    INNER JOIN ( SELECT FK_Product, MAX(Version) AS
    max_version
    FROM MyTable
    GROUP BY FK_Product
    ) as ver ON lan.FK_Product = ver.FK_Product AND
    lan.Version = ver.max_version

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

    This works exactly like i requested :)

    There is one problem left i forgot at first: There may be
    cases, where neither Language=0 nor Language=language is
    available. This should not happen by "buisness rules".
    But IF it happens, I would like to get the pdf from the
    next lower Version... But I try to figure this out by
    myself. Thanks for your help!!!

    - Oliver

    Oliver Guest

Similar Threads

  1. Replies: 0
    Last Post: August 26th, 06:01 AM
  2. "extract pages" greyed out (version 9pro...but also in version 7!)
    By Darren_Linkin@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 2
    Last Post: August 3rd, 03:56 PM
  3. "graphic select" vs "image select"
    By Gary_A.@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 5
    Last Post: May 21st, 02:39 PM
  4. FScommand("Exec","myapplication.exe") doesnot work on Trial version MX?
    By yn webforumsuser@macromedia.com in forum Macromedia Flash Actionscript
    Replies: 1
    Last Post: January 13th, 08:45 AM
  5. "Select" & "Order By" OK- ''WHERE'' Does'nt Work !
    By ian justice in forum PHP Development
    Replies: 23
    Last Post: November 20th, 06:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139