Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    1

    Unanswered: Nested SQL too slow

    Hello

    I am using a nested SQL to get certain data (shown below).
    What I need is the latest value for an entity in Column1.

    something like
    A 84
    B 85
    C 81
    D 87
    E 78

    The current SQL i use is something like this
    select
    a.column1,
    a.value
    from table1 a
    where a.DTCol = (select max(b.DTCol)
    from table1 b
    where a.PK = b.PK)

    Can somebody tell what a decent SQL would be
    Some sort of logic which groups the fields by Column1 name sorts on descending date.

    I have table data as follows: table1

    PK Column1 DTCol Value
    1 A 10/1/2003 10
    2 B 10/1/2003 11
    3 C 10/1/2003 12
    4 D 10/1/2003 14

    5 A 10/2/2003 20
    6 B 10/2/2003 22
    7 C 10/2/2003 23
    8 D 10/2/2003 24

    9 A 10/3/2003 30
    10 B 10/3/2003 31
    11 C 10/3/2003 32

    12 A 10/3/2003 50
    13 B 10/4/2003 51
    14 C 10/4/2003 52
    15 E 10/4/2003 78

    16 A 10/5/2003 79
    17 B 10/5/2003 80
    18 C 10/5/2003 81
    19 D 10/5/2003 82

    20 A 10/6/2003 83
    21 B 10/6/2003 84
    23 D 10/6/2003 86

  2. #2
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    slow

    first of all have you got an index on DTCol?

    second:
    I don*t know exaclt whether you need to join the table itself


    try:
    select
    .column1,
    a.value
    from table1AS a
    where a.DTCol = (select max(a.DTCol)
    from table1)

    the where clause is absolute nonsense

    why do you join the same two tables if you want the maximum of one table
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

Posting Permissions

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