urmatorul select da eraore pt ca nu am campurile din order by in select distinct....

fara distinct rezultatul e cu duplicate...
 

SELECT DISTINCT A.x, A.y, A.z, B.m, B.n, B.q
FROM (B INNER JOIN A ON B.w=A.w)
WHERE A.x = '1'
ORDER BY B.f, B.g, B.h

cum pot elimina duplicatele si la order by sa am alte campuri decat in select????

multumesc mult.... 

Poti incerca 

SELECT DISTINCT Q.h  Q.x, Q.y, Q.z, Q.m, Q.n, Q.q

FROM

(
SELECT B.f, B.g, B.h, B.h  A.x, A.y, A.z, B.m, B.n, B.q
FROM (B INNER JOIN A ON B.w=A.w)
WHERE A.x = '1'
ORDER BY B.f, B.g, B.h

) AS Q 

DESI ... DESI ... DESI ...

si ce se intampla daca cele doua tabele din join au coloane cu acelasi nume?

imi da urmatoarea eroare:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified....

deci nu pot pune order by in subquery?

merci... 

B_gd_n[ ]Sahlean:

...

DESI ... DESI ... DESI ...

ioana:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified....

deci nu pot pune order by in subquery?

SELECT DISTINCT Q.h  Q.x, Q.y, Q.z, Q.m, Q.n, Q.q

FROM

(
SELECT TOP 100 PERCENT B.f, B.g, B.h, B.h  A.x, A.y, A.z, B.m, B.n, B.q
FROM (B INNER JOIN A ON B.w=A.w)
WHERE A.x = '1'
ORDER BY B.f, B.g, B.h

) AS Q

Ioana,

Mesajul de eroare apare pentru că nu prea are sens ceea ce ceri. Să luăm în considerare următorul exemplu:

CREATE TABLE T (
    a int,
    b int,
    c int,
    PRIMARY KEY (a,b,c)
)

INSERT INTO T VALUES (1,10,500)
INSERT INTO T VALUES (2,10,400)
INSERT INTO T VALUES (2,10,200)
INSERT INTO T VALUES (3,10,300)

Dacă încercăm următoarea instrucţiune:

SELECT DISTINCT a,b FROM T ORDER BY b,c

atunci primim eroarea menţionată ("ORDER BY items must appear in the select list if SELECT DISTINCT is specified."). Însă, în definitiv, care ar fi fost rezultatul corect ?

a) luând în considerare rândul 2,10,200

2,10
3,10
1,10

sau

b) luând în considerare rândul 2,10,400

3,10
2,10
1,10

Evident, cerinţa nu e suficient de clară, aşa că nu există un rezultat corect.

Pe de altă parte, să încercăm un query cu soluţia sugerată de Bogdan:

SELECT DISTINCT a,b FROM (
    SELECT TOP 100 PERCENT * FROM T ORDER BY b,c
) x

Observăm că rezultatul este:

1,10
2,10
3,10

deci nu se respectă ordinea specificată în subquery. Cred că exact la asta se referea Bogdan când scria "deşi... deşi... deşi...": faptul că deşi uneori ni se pare că ORDER BY-ul cu TOP 100 PERCENT funcţionează în view-uri şi subquery-uri, este de fapt doar o întâmplare.

Am putea să rezovăm această problemă dacă modificăm (sau clarificăm, mai bine zis) cerinţele: spunem că de fapt nu vrem să se ordoneze după coloana c, ci după cea mai mică valoare din coloana c pentru fiecare rând distinct selectat. Adică, optăm pentru rezultatul menţionat la litera a), mai sus, iar în acest caz, query-ul ar fi:

SELECT a,b FROM T GROUP BY a,b ORDER BY b,MIN(c)

Răzvan

rsocol:

Mesajul de eroare apare pentru că nu prea are sens ceea ce ceri.

Razvan: in opinia mea lucrurile nu prea sunt chiar asa.Totul depinde de modul in care motorul SGBD-ului (M-SGBD) executa interogarea. Voi incerca sa ma explic.

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Mesajul de eroare "ORDER BY items must appear in the select list if SELECT DISTINCT is specified." demonstreaza o LIMITARE a motorului sistemului de gestiune a bazelor de date (SQL Server database engine), limitarea care provine din solutia pe care o alege M-SGBD pentru a elimina duplicatele ca urmare a existentei clauzei DISTINCT in comanda SQL.

Pornind de la exemplu cu tabela T amintit de tine daca vom executa comanda SELECT  a,b FROM T ORDER BY b,c vom obtine setul de inregistrari:

a     b     c    (coloana c este prezentata cu scop orientativ)

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

2    10    200           (inregistrarea I)
3    10    300           (inregistrarea II)
2    10    400           (inregistrarea III)
1    10    500            (inregistrarea IV)

Ca urmare a executiei unei posibile comenzi  SQL SELECT DISTINCT  a,b FROM T ORDER BY b,c M-SGBD ar trebui sa elimine una din inregistrarile I sau III (sunt subliniate). Acest fapt ar putea conduce la urmatoarele POSBILE seturi de inregistrari rezultate:

(a) daca se elimina inregistrarea duplicat I (mai precis perechea de valori 2 - 10) rezultatul ar trebui sa fie:

 3    10    300           (inregistrarea II)
2    10    400           (inregistrarea III rămâne)
1    10    500            (inregistrarea IV)

sau

(b) daca se elimina inregistrarea duplicat III (mai precis perechea de valori 2 - 10) rezultatul ar trebui sa fie:

2    10    200           (inregistrarea I rămâne)
3    10    300           (inregistrarea II)
1    10    500            (inregistrarea IV)

Atat in cazul (a) cat si in cazul (b) inregistrarile sunt ordonate după valorile din campurile b şi c.

In concluzie cerinta are sens  dpmdv, problema  este la  SQL Server pentru ca  acesta  va incerca sa minimizeze timpul de executie a interogarii SQL in ceea ce priveste clauza DISTINCT prin ordonarea/sortarea (ORDER BY) sau grupare inregistrarilor (GROUP BY, care in ultima instanta presupune tot o sortare).

Cu alte cuvinte daca vom avea o interogare SQL SELECT DISTINCT  a,b FROM ... M-SGBD va ordona (sorta) sau grupa (a se vedea imaginea) inregistrarile dupa (valorile din campurile) a si b (campuri specificate in clauza SELECT DISTINCT).

 In concluzie este normal din acest punct de vedere ca IN FINAL inregistrarile sa fie ordonate dupa valorile campurilor din SELECT DISTICNT si anume a si b.

Ultima concluzie: cerinta nu este "imposibila". Limitarile SQL Server fac ca acesta interogare sa fie "imposibila".

 
PS : si da, "deşi-urile" de la finalul primul mesaj exprima scepticismul meu in legatura cu ordinea in care vor fi afisate IN FINAL inregistrarile din subinterogare.

Din câte am citit, aceeaşi limitare există şi în Oracle, PosgreSQL, Sybase, Access, etc, deoarece este prevăzută de standardul ANSI-SQL: la o citire superificială a standardului ISO 9075-2:2003, am găsit o prevedere de genul acesta în capitolul 14.1, la regula de sintaxă 18) lit. d) pct.i) 9) B) II). Totuşi, se pare că există un SGBD care ignoră această regulă, anume MySQL, care ia în considerare (la întâmplare) unul dintre rândurile întâlnite.

Răzvan

Alta solutie , putin mai complexa comparativ cu cea oferita de Razvan (se pleaca de la ideea ca exista in setul de inregistrari o cheie primara/candidat):

Tabela are structura T(id cheie primara, a, b, c) 

SELECT a, b
FROM T
WHERE id in (SELECT MAX(id) AS id FROM T GROUP BY a,b)
ORDER BY b,c

rsocol:

[...] deşi uneori ni se pare că ORDER BY-ul cu TOP 100 PERCENT funcţionează în view-uri şi subquery-uri, este de fapt doar o întâmplare.

Câteva clarificări în legătură cu fraza de mai sus:
http://blogs.msdn.com/queryoptteam/archive/2006/03/24/560396.aspx

Răzvan 

rsocol:

rsocol:

[...] deşi uneori ni se pare că ORDER BY-ul cu TOP 100 PERCENT funcţionează în view-uri şi subquery-uri, este de fapt doar o întâmplare.

Câteva clarificări în legătură cu fraza de mai sus:
http://blogs.msdn.com/queryoptteam/archive/2006/03/24/560396.aspx

Răzvan 

... 

create view v as (select top 100 percent * from t1 order by col1 desc)

...
select * from v

...

Este de inteles faptul ca in urma executiei interogarii select * from v se obtine POATE un set de inregistrari neordonate/nesortate. Si asta intr-adevar pentru ca in aceasta interogare lipseste clauza ORDER BY (select * from v order by ...). De asemenea, din analiza planului de executie se observa clar faptul ca M-SGBD (database engine) igonora clauza ORDER BY din definitia vederii select top 100 percent * from t1 order by col1 desc

in momentul executiei interogarii  select * from v

  |--Table Scan(OBJECT:([tempdb].[dbo].[t1])) 

Dar, daca cream o vedere cu urmatoarea definitie create view v2 as (select col1, col2 from t1 group by col1, col2)

in urma executiei interogarii select * from v2 rezultatul va fi altul:  inregistrarile sunt ordonate desi lipseste indexul "cluster".

  |--Sort(DISTINCT ORDER BY:([tempdb].[dbo].[t1].[col1] ASC, [tempdb].[dbo].[t1].[col2] ASC))
       |--Table Scan(OBJECT:([tempdb].[dbo].[t1]))

Chiar si in acest ultim caz nu este o garantie (teoretic si practic) asupra faptului ca IN FINAL (select * from v2)  setul de inregistrari va fi ordonat conform criteriilor din definitia vederii v2 ( create view v2 as ( ... group by col1, col2) ).

Dacă scriem un query de genul "SELECT c FROM t GROUP BY c" sau "SELECT DISTINCT c FROM t" nu înseamnă că vom obţine înregistrările respective ordonate după coloana c (deşi uneori se întâmplă asta). De exemplu, să considerăm următoarele query-uri în AdventureWorks:

SELECT Availability, COUNT(*) FROM Production.Location GROUP BY Availability
SELECT ProductID, COUNT(*) FROM Production.TransactionHistory GROUP BY ProductID
SELECT ReferenceOrderLineID, COUNT(*) FROM Production.TransactionHistory GROUP BY ReferenceOrderLineID
SELECT Quantity, COUNT(*) FROM Production.TransactionHistory GROUP BY Quantity

Rezultatele primele două query-uri apar sortate după coloana care e menţionată în GROUP BY, dar la celelalte două query-uri, rezultatele nu mai sunt sortate. Dacă nu specificăm clauza ORDER BY, atunci SQL-ul poate să returneze datele în orice ordine, având libertatea să aleagă query plan-ul care pare cel mai rapid. Acest lucru îl face în funcţie de indecşii disponibili, dar şi de volumul datelor din fiecare tabelă, precum şi de distribuţia valorilor din coloanele implicate.

Pe de altă parte, există o opinie greşită că dacă se face un SELECT fără ORDER BY, atunci rezultatele vor apare totdeauna în ordinea indexului clustered. Acest lucru se poate întâmpla în multe cazuri, dar nu totdeauna. De exemplu, să considerăm următorul query:

SELECT * FROM Production.TransactionHistory WHERE ReferenceOrderID=53457

Deşi indexul clustered din această tabelă este pe coloana TransactionID, rezultatele obţinute nu sunt ordonate după această coloană. În acest caz, rezultatele au fost ordonate după ReferenceOrderID+ReferenceOrderLineID, deoarece s-a folosit acest index non-clustered pentru a efectua filtrarea. Vezi şi următoarea discuţie pentru mai multe amănunte: http://www.developersdex.com/sql/message.asp?p=581&r=5875444

Concluzia: dacă în decursul testării obţineţi rezultatele ordonate aşa cum doriţi, chiar dacă nu aţi specificat clauza ORDER BY, pe măsură ce tabela va conţine mai multe date este posibil ca planul de execuţie să fie altul, iar rezultatele să nu mai apară în aceeaşi ordine. Deci dacă doriţi ca rezultatele să apară într-o anumită ordine, specificaţi clauza ORDER BY în query-urile respective.

Răzvan

[1] Exemplul
create view v2 as (select col1, col2 from t1 group by col1, col2)
plus
select * from v2
este un caz particular. Nu am generalizat dupa cum se observa si din ultima afirmatie din mesajul 2435: " ... nu este o garantie (teoretic si practic) asupra faptului ca IN FINAL ...  setul de inregistrari va fi ordonat  ... "

[2] Nu am afirmat ca "SELECT fără ORDER BY, atunci rezultatele vor apare totdeauna în ordinea indexului clustered" sau ceva asemanator.
Ce am dorit sa sugerez (dar e mult spus) a fost faptul ca existenta unui index "clustered" creste (mult/foarte mult) probabilitatea de utilizara a acestui index "clustered" de catre SQL Server.

 

Ai dreptate. Nu am vrut să te contrazic, ci să clarific unele aspecte care ar putea fi neclare pentru alţi cititori.

Răzvan