Distinct at two columns

I have this stored procedure:

ALTER PROCEDURE usp_My_Procedure
(
@.Country varchar(5)
)

AS
SELECT DISTINCT City, Short FROM Table1 WHERE Country = @.Country

RETURN

I want to select just one of each 'city' and 'short' in the database...But this is not working correct.....Whats wrong?

Lets say that I have a table that looks something like this

City Short

New York NY

Los Angeles LA

Lake Alice LA

Los Angeles LosAng

well ur code like this is anylized like give everything not repeated for these two columns,

try this

SELECT City, Short FROM Table1 WHERE Country = @.Country

and City in (SELECT DISTINCT City FROM Table1 WHERE Country = @.Country)

and Short in (SELECT DISTINCT short FROM Table1 WHERE Country = @.Country)

sql

Distinct aggregation and Sql99 Olap functions

Hi,
I noticed that SS2K5 doesn't support distinct aggregation with sql99 olap
syntax. e.g.
select country, count(distinct userid) over (partition by country), ... from
...
I know this is also not supported by DB2/TeraData, but supported by
Oracle. Conceptually, I don't see an obvious reason why distinct aggregation
shouldn't not be supported by SS2k5. Is this just strict interpretation of
the Sql99 standard or is there a more fundamental reason? Any insight would
be greatly appreciated.I can imagine that it's just an implementation issue that would require more
development resources.
There are other aspects of the OVER clause which I find more important that
were not implemented yet (ORDER BY, ROWS/RANGE clauses for window based
aggregates).
BTW, you're particular request can be satisfied with the following
alternative:
USE Northwind;
WITH C AS
(
SELECT ShipCountry,
DENSE_RANK() OVER(PARTITION BY ShipCountry ORDER BY CustomerID) AS DRnk
FROM dbo.Orders
)
SELECT ShipCountry, MAX(DRnk) OVER(PARTITION BY ShipCountry) AS DCntCust
FROM C;
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"Tim" <Tim@.discussions.microsoft.com> wrote in message
news:9416446C-F097-416E-BA57-7AA538B46D8F@.microsoft.com...
> Hi,
> I noticed that SS2K5 doesn't support distinct aggregation with sql99
> olap
> syntax. e.g.
> select country, count(distinct userid) over (partition by country), ...
> from
> ...
> I know this is also not supported by DB2/TeraData, but supported by
> Oracle. Conceptually, I don't see an obvious reason why distinct
> aggregation
> shouldn't not be supported by SS2k5. Is this just strict interpretation of
> the Sql99 standard or is there a more fundamental reason? Any insight
> would
> be greatly appreciated.|||I have a feeling that you will ask, what if I want a SUM(DISTINCT)...?
USE pubs;
WITH C AS
(
SELECT stor_id, qty,
ROW_NUMBER() OVER(PARTITION BY stor_id, qty
ORDER BY stor_id, qty) AS rn
FROM dbo.sales
)
SELECT stor_id,
SUM(CASE WHEN rn = 1 THEN qty END) OVER(PARTITION BY stor_id) AS dsumqty
FROM C;
;-)
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:ug7tle60FHA.3376@.TK2MSFTNGP14.phx.gbl...
>I can imagine that it's just an implementation issue that would require
>more development resources.
> There are other aspects of the OVER clause which I find more important
> that were not implemented yet (ORDER BY, ROWS/RANGE clauses for window
> based aggregates).
> BTW, you're particular request can be satisfied with the following
> alternative:
> USE Northwind;
> WITH C AS
> (
> SELECT ShipCountry,
> DENSE_RANK() OVER(PARTITION BY ShipCountry ORDER BY CustomerID) AS DRnk
> FROM dbo.Orders
> )
> SELECT ShipCountry, MAX(DRnk) OVER(PARTITION BY ShipCountry) AS DCntCust
> FROM C;
>
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> Join us for the SQL Server 2005 launch at the SQL W in Israel!
> [url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
>
> "Tim" <Tim@.discussions.microsoft.com> wrote in message
> news:9416446C-F097-416E-BA57-7AA538B46D8F@.microsoft.com...
>

displaying null fields

i have a query that gets orders made, visits to the page, and revenue produced for each day in a month and country we ship to.

when there are no visits to the page, then there are no orders and no revenue is produces, so those fields are null. However, instead of displaying a row with the date and country and null values for the rest, the query just skips those rows entirely.

is there anyway to get what I want either using crystal or the sql (i.e, a list of every day in the month with the specific info for that day, regardless if it's null (cause i can always replace the null values with something else))?

P.S. it's an Oracle DBCan you post your SQL and the selection formula?
Do you really mean that you have data whose values are NULL or are you, for example, joining to an orders table and the lack of orders means there is no join so there is no data?|||I think istead of Inner join, you need to use Left outer Join