Filtering group of selected data by the latest dates for specific article

Question

I'm trying to get data from SQL database with SELECT:

SELECT DISTINCT T2D.NO_ARTIKLU,
                T2D.NAZEV_ARTIKLU,
                T2D.DATUM,
                T10.NO_PARTNERA
    FROM D3000S.DIA2ZZ0D T2D, D3000S.DIA1ZZ00 T10, D3000S.DIA2ZZ00 T20
    WHERE T10.NO_PARTNERA = T2D.NO_PARTNERA AND T2D.AKTIVITA > 0

The problem is -> There are most of duplicates that I want to filter with SELECT and I need to show only article with the latest datetime.

Thank you for the answers.

**Always use proper, explicit, *standard* `JOIN` syntax.** *Never* use commas in the `FROM` clause. — Gordon Linoff, Nov 27 '19 at 11:58
Show us some sample table data and the expected result, all as formatted text (not images.) And take a look at [mcve]. — jarlh, Nov 27 '19 at 12:00
I just need to help how to filter that records by the latest datetime in T2D.DATUM, And Im so sorry but I can not share any data — Swerph, Nov 27 '19 at 12:05
Try to use [group by](http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36272.1570/html/commands/X96182.htm). — csaar, Nov 28 '19 at 07:38

score 0 · Answer 1 · answered Nov 27 '19 at 12:14

0

Please see below query .Rank can be used to eliminate the duplicate values.

  SELECT * FROM
  (
 SELECT DISTINCT T2D.NO_ARTIKLU,
            T2D.NAZEV_ARTIKLU,
            T2D.DATUM,
            T10.NO_PARTNERA,Row_Number() OVER(PARTITION BY Duplicatidentifier ORDER 
    By Datetimecolumn desc)
    AS Rank 
     FROM D3000S.DIA2ZZ0D T2D, D3000S.DIA1ZZ00 T10, D3000S.DIA2ZZ00 T20
    WHERE T10.NO_PARTNERA = T2D.NO_PARTNERA AND T2D.AKTIVITA > 0
   ) AS B WHERE Rank=1

answered Nov 27 '19 at 12:14

Its_Ady

308
2
10

Thank you for the answer, but that looks like not correct syntax: SQL Error [156] [S1000]: Incorrect syntax near the keyword 'OVER' – Swerph Nov 27 '19 at 12:21
@Swerph did you changed the column names.? – Its_Ady Nov 27 '19 at 13:31
Yes of course, Looks like my SQL engine doesn't support this type of commands – Swerph Nov 27 '19 at 14:04

score 0 · Answer 2 · answered Nov 27 '19 at 12:21

Firstly, echoing a previous comment, you should use ANSI join syntax. Reasons include increased clarity and support for full outer joins. The topic is discussed here

An obvious problem with your query, is that you do not join the third table to anything. Fixing this gives:

SELECT DISTINCT T2D.NO_ARTIKLU,
                T2D.NAZEV_ARTIKLU,
                T2D.DATUM,
                T10.NO_PARTNERA
    FROM D3000S.DIA2ZZ0D T2D
    INNER JOIN D3000S.DIA1ZZ00 T10 ON T10.NO_PARTNERA = T2D.NO_PARTNERA
    INNER JOIN D3000S.DIA2ZZ00 T20 ON T10.NO_PARTNERA = T20.NO_PARTNERA
    WHERE T2D.AKTIVITA > 0

Filtering group of selected data by the latest dates for specific article

2 Answers2