Returning more than one value from a sql statement

Question

I was looking at sql inner queries (bit like the sql equivalent of a C# anon method), and was wondering, can I return more than one value from a query?

For example, return the number of rows in a table as one output value, and also, as another output value, return the distinct number of rows?

Also, how does distinct work? Is this based on whether one field may be the same as another (thus classified as "distinct")?

I am using Sql Server 2005. Would there be a performance penalty if I return one value from one query, rather than two from one query?

Thanks

Answer 1

You could do your first question by doing this:

SELECT
  COUNT(field1),
  COUNT(DISTINCT field2)
FROM table

(For the first field you could do * if needed to count null values.)

Distinct means the definition of the word. It eliminates duplicate returned rows.

Returning 2 values instead of 1 would depend on what the values were, if they were indexed or not and other undetermined possible variables.

If you are meaning subqueries within the select statement, no you can only return 1 value. If you want more than 1 value you will have to use the subquery as a join.

Answer 2

If the inner query is inline in the SELECT, you may struggle to select multiple values. However, it is often possible to JOIN to a sub-query instead; that way, the sub-query can be named and you can get multiple results

SELECT a.Foo, a.Bar, x.[Count], x.[Avg]
FROM a
INNER JOIN (SELECT COUNT(1) AS [Count], AVG(something) AS [Avg]) x
  ON x.Something = a.Something

Which might help.

DISTINCT does what it says. IIRC, you can SELECT COUNT(DISTINCT Foo) etc to query distinct data.

Answer 3

Inner queries in the form: SELECT * FROM tbl WHERE fld in (SELECT fld2 FROM tbl2 WHERE tbl.fld = tbl2.fld2) cannot return multiple rows. When you need multiple rows from a secondary query, you usually need to do an inner join on the other query.

rows: SELECT count(*), count(distinct *) from table will return a dataset with one row containing two columns. Column 1 is the total number of rows in the table. Column 2 counts only distinct rows.

Distinct means the returned dataset will not have any duplicate rows. Distinct can only appear once usually directly after the select. Thus a query such as SELECT distinct a, b, c FROM table might have this result:

 a1  b1  c1
 a1  b1  c2
 a1  b2  c2
 a1  b3  c2

Note that values are duplicated across the whole result set but each row is unique.

I'm not sure what your last question means. You should return from a query all the data relevant to the query. As for faster, only benchmarking can tell you which approach is faster.

Answer 4

you can return multiple results in 3 ways (off the top of my head)

1. By having a select with multiple values eg: select col1, col2, col3
2. With multiple queries eg: select 1 ; select "2" ; select colA. you would get to them in a datareader by calling .NextRecord()
3. Using output parameters, declare the parameters before exec the query then get the value from them afterwards. eg: set @param1 = "2" . string myparam2 = sqlcommand.parameters["param1"].tostring()

Distinct, filters resulting rows to be unique.