Order by \"relavans\"

kan du bruke dette til noe:

SELECT *

FROM
[tabell]

WHERE
(CompanyName LIKE \'Madison\' AND Descpi LIKE \'Madison\')

OR

UNION ALL

(CompanyName LIKE \'Madison\' OR Descpi LIKE \'Madison\')

/nute

hehe nej! :o)

hmmm.....det var da noe lort :(....hvorfor ikke ?

prøv selv at teste det mod en test tabel!

hvilken type db sitter du med ?

Access, MSSql, Oracle, MySql ?

hmmm....UNION ALL gir forresten dubletter, og det vil du jo ikke ha. jeg bommet også litt med syntaxen. Prøv denne:

SELECT *

FROM
[tabell]

WHERE
CompanyName LIKE \'Madison\' AND Descpi LIKE \'Madison\'

OR

UNION

SELECT *

FROM
[tabell]

WHERE
CompanyName LIKE \'Madison\' OR Descpi LIKE \'Madison\'

/nute

GROUP BY Clause:

Specifies the groups into which output rows are to be placed and, if aggregate functions are included in the SELECT clause <select list>, calculates a summary value for each group. When a GROUP BY clause is used, each item in the select list must produce a single value for each group. Null values in one item are placed in one group.

Note  If the ORDER BY clause is not specified, groups returned using the GROUP BY clause are not in any particular order. It is recommended that you always use the ORDER BY clause to specify a particular ordering of the data.

Syntax

[ GROUP BY [ALL] group_by_expression [,...n]
        [ WITH { CUBE | ROLLUP } ]
]

Arguments

ALL

Includes all groups and result sets, even those that do not have any rows that meet the search condition specified in the WHERE clause. When ALL is specified, null values are returned for the summary columns of groups that do not meet the search condition. You cannot specify ALL with the CUBE or ROLLUP operators.
GROUP BY ALL is not supported in queries that access remote tables.

group_by_expression

Is an expression on which grouping is performed. group_by_expression is also known as a grouping column. group_by expression can be a column or a nonaggregate expression that references a column. A column alias that is defined in the select list cannot be used to specify a grouping column. When GROUP BY is specified, any column in the select list (except a column used in an aggregate function) must be specified as a grouping column.

Note  Columns of type text, ntext, image, and bit cannot be used in group_by_expression.

For GROUP BY clauses that do not contain CUBE or ROLLUP, the number of group_by_expression items is limited by the GROUP BY column sizes, the aggregated columns, and the aggregate values involved in the query. This limit originates from the limit of 8,060 bytes on the intermediate work table that is needed to hold intermediate query results. A maximum of 10 grouping expressions is permitted when CUBE or ROLLUP is specified.

CUBE

Specifies that, in addition to the usual rows provided by GROUP BY, summary rows are introduced into the result set. A GROUP BY summary row is returned for every possible combination of group and subgroup in the result set. A GROUP BY summary row is displayed as NULL in the result, but is used to indicate all values. Use the GROUPING function to determine whether null values in the result set are GROUP BY summary values.
The number of summary rows in the result set is determined by the number of columns included in the GROUP BY clause. Each operand (column) in the GROUP BY clause is bound under the grouping NULL and grouping is applied to all other operands (columns). Because CUBE returns every possible combination of group and subgroup, the number of rows is the same, regardless of the order in which the grouping columns are specified.

ROLLUP

Specifies that, in addition to the usual rows provided by GROUP BY, summary rows are introduced into the result set. Groups are summarized in a hierarchical order, from the lowest level in the group to the highest. The group hierarchy is determined by the order in which the grouping columns are specified. Changing the order of the grouping columns can affect the number of rows produced in the result set.

Important  Distinct aggregates, for example, AVG(DISTINCT column_name), COUNT(DISTINCT column_name), and SUM(DISTINCT column_name), are not supported when using CUBE or ROLLUP. If used, SQL Server returns an error message and cancels the query.


ORDER BY Clause:
Specifies the sort for the result set. Subqueries and view definitions cannot include an ORDER BY clause.
Syntax

[ORDER BY {order_by_expression [ ASC | DESC ] }     [,...n]    ]

Arguments

order_by_expression

Specifies a column on which to sort. A sort column can be specified as a name or column alias (which can be qualified by the table or view name), an expression, or a nonnegative integer representing the position of the name, alias, or expression in select list.
Multiple sort columns can be specified. The sequence of the sort columns in the ORDER BY clause defines the organization of the sorted result set.
The ORDER BY clause can include items not appearing in the select list; however, if SELECT DISTINCT is specified or if the SELECT statement contains a UNION operator, the sort columns must appear in the select list.

Furthermore, when the SELECT statement includes a UNION operator, the column names or column aliases must be those specified in the first select list.

Note  ntext, text, or image columns cannot be used in an ORDER BY clause.

ASC

Specifies that the values in the specified column should be sorted in ascending order, from lowest value to highest value.

DESC

Specifies that the values in the specified column should be sorted in descending order, from highest value to lowest value.

Null values are treated as the lowest possible values.
There is no limit to the number of items in the ORDER BY clause; however, there is a limit of 8,060 bytes, for the row size of intermediate worktables needed for sort operations. This limits the total size of columns specified in an ORDER BY clause.

lidt mere vedr. JOIN:

Use the SQL-92 INNER JOIN syntax

This example returns all publisher names with the corresponding book titles each publisher has published.

USE pubs

-- By default, SQL Server performs an INNER JOIN if only the JOIN

-- keyword is specified.

SELECT SUBSTRING(titles.title, 1, 30) AS Title, publishers.pub_name

FROM publishers INNER JOIN titles

ON titles.pub_id = publishers.pub_id
ORDER BY publishers.pub_name
 

Here is the result set:

Title                          pub_name                               

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

The Busy Executive\'s Database  Algodata Infosystems                   

Cooking with Computers: Surrep Algodata Infosystems                   

Straight Talk About Computers  Algodata Infosystems                   

But Is It User Friendly?      Algodata Infosystems                   

Secrets of Silicon Valley      Algodata Infosystems                   

Net Etiquette                  Algodata Infosystems                   

Silicon Valley Gastronomic Tre Binnet & Hardley                       

The Gourmet Microwave          Binnet & Hardley                       

The Psychology of Computer Coo Binnet & Hardley                       

Computer Phobic AND Non-Phobic Binnet & Hardley                       

Onions, Leeks, and Garlic: Coo Binnet & Hardley                       

Fifty Years in Buckingham Pala Binnet & Hardley                       

Sushi, Anyone?                Binnet & Hardley                       

You Can Combat Computer Stress New Moon Books                         

Is Anger the Enemy?            New Moon Books                         

Life Without Fear              New Moon Books                         

Prolonged Data Deprivation: Fo New Moon Books                         

Emotional Security: A New Algo New Moon Books                         

 

(18 row(s) affected)

 

Use the SQL-92 RIGHT OUTER JOIN syntax

This example joins two tables on pub_id and preserves the unmatched rows from the right table. The publishers table is matched with the titles table on the pub_id column in each table. All publishers appear in the result set, whether or not they have published any books.

USE pubs

SELECT SUBSTRING(titles.title, 1, 30) AS \'Title\', publishers.pub_name

FROM titles RIGHT OUTER JOIN publishers

ON titles.pub_id = publishers.pub_id
ORDER BY publishers.pub_name
 

Here is the result set:

Title                          pub_name                               

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

The Busy Executive\'s Database  Algodata Infosystems                   

Cooking with Computers: Surrep Algodata Infosystems                   

Straight Talk About Computers  Algodata Infosystems                   

But Is It User Friendly?      Algodata Infosystems                   

Secrets of Silicon Valley      Algodata Infosystems                   

Net Etiquette                  Algodata Infosystems                   

Silicon Valley Gastronomic Tre Binnet & Hardley                       

The Gourmet Microwave          Binnet & Hardley                       

The Psychology of Computer Coo Binnet & Hardley                       

Computer Phobic AND Non-Phobic Binnet & Hardley                       

Onions, Leeks, and Garlic: Coo Binnet & Hardley                       

Fifty Years in Buckingham Pala Binnet & Hardley                       

Sushi, Anyone?                Binnet & Hardley                       

NULL                          Five Lakes Publishing                   

NULL                          GGG&G                                   

NULL                          Lucerne Publishing                     

You Can Combat Computer Stress New Moon Books                         

Is Anger the Enemy?            New Moon Books                         

Life Without Fear              New Moon Books                         

Prolonged Data Deprivation: Fo New Moon Books                         

Emotional Security: A New Algo New Moon Books                         

NULL                          Ramona Publishers                       

NULL                          Scootney Books                         

 

(23 row(s) affected)

 

H.    Use HASH and MERGE join hints

This example performs a three-table join among the authors, titleauthors, and titles tables to produce a list of authors and the books they have written. The query optimizer joins authors and titleauthors (A x TA) using a MERGE join. Next, the results of the authors and titleauthors MERGE join (A x TA) are HASH joined with the titles table to produce (A x TA) x T.

Important  After a join hint is specified, the INNER keyword is no longer optional and must be explicitly stated for an INNER JOIN to be performed.

USE pubs

SELECT SUBSTRING((RTRIM(a.au_fname) + \' \' + LTRIM(a.au_lname)), 1, 25)
    AS Name, SUBSTRING(t.title, 1, 20) AS Title
FROM authors a INNER MERGE JOIN titleauthor ta
    ON a.au_id = ta.au_id INNER HASH JOIN titles t
    ON t.title_id = ta.title_id
ORDER BY au_lname ASC, au_fname ASC
 

Here is the result set:

Warning: The join order has been enforced because a local join hint is used.

Name                      Title               

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

Abraham Bennet            The Busy Executive\'s

Reginald Blotchet-Halls  Fifty Years in Bucki

Cheryl Carson            But Is It User Frien

Michel DeFrance          The Gourmet Microwav

Innes del Castillo        Silicon Valley Gastr

...                          ...
Johnson White            Prolonged Data Depri
Akiko Yokomoto            Sushi, Anyone?     
 
(25 row(s) affected)

codebase >> kan du ikke heller poste en link til en site hvor dette befinner seg istedenfor å poste en hel masse (tildels) out of context ting...

det kan jo godt være du ikke kan bruge dt, men det er jo heller ikke dit spg. !! :)

_codeb@se.


PS: jeg mener ikke det er \'out of context\' da der er tale om en JOIN sætning der skal GROUP/ORDER BY !!

jepp, men det kan jo hende at skysurfer vil se \"hele\" dokumentasjonen i sin helhet, eller at han vil få forklart hva det egentlig står der...

men på en annen side så har du rett i det du sier, men hadde jeg hatt postet et spm her inne og fått spyttet alt det du har postet i hodet mitt, så hadde jeg ignorert det, for dokumentasjon er noe man alltid får bruk for, og det å finne fram til biter av dokumentasjon her inne på xperten, er ikke det optimale... ;)

jeg har ikke noget link til den info. da\' det er et Sql program. der hedder helpsql :)

_codeb@se.

jepp...har det samme selv ;)

Det jeg ledte efter var et konkret eksempel ud fra de opl. jeg gav! Dvs et eks, jeg nærmest kan kopiere ind i min Query Analyser for at teste det mod alle mine records.

:)

og du kan fremdeles ikke bruke det jeg sendte ?

den skriver at der er en wront syntaks near UNION

Bøh - jeg fandt ud af det ved hjælp af INdex server!

Bøh - jeg fandt ud af det ved hjælp af INdex server!