SELECT DISTINCT And ORDER BY With Aliased Column

Feb 14, 2008



This query demonstrates a problem I have run across:


USE AdventureWorks

GO

-- This query works fine.

SELECT DISTINCT FirstName AS Name1 FROM Person.Contact ORDER BY FirstName

GO

-- This query also works fine.

SELECT ISNULL(FirstName, '') AS Name1 FROM Person.Contact ORDER BY FirstName

GO

-- This query returns error 145

SELECT DISTINCT ISNULL(FirstName, '') AS Name1 FROM Person.Contact ORDER BY FirstName

GO


The last query returns the error "ORDER BY items must appear in the select list if SELECT DISTINCT is specified".
It will work if I change ORDER BY to use "Name1" instead of "FirstName", but in the situation I have at hand, the query is generated by third-party software and I don't have the ability to change it.
Can anyone explain why what's going on here? Oddly, this same query will work if I run it against SQL Server 2000.

View 8 Replies


ADVERTISEMENT

How Do I Use Order By When I Use Select Distinct.

May 6, 2008

Hi
    I have a query which returns some rows.. what happens if i use a select distinct instead of a select.. this is my sproc
 DECLARE @Counter TABLE(
PlanId int,
FundId int,
ClientFundName varchar(110),
DisplayOrder int IDENTITY(1,1),
IsDefault bit,
IsPortfolioFundOnly bit
)
INSERT INTO @Counter
(
PlanId,
FundId,
ClientFundName,
IsDefault,
IsPortfolioFundOnly
)
SELECT
5923,
f.FundId,
d.FundName,
CASE WHEN d.FundDefault IS NULL THEN 0 ELSE 1 END,
CASE WHEN Lower(p.FundType) = 'modfundonly' THEN 1 ELSE 0 END
FROM
PlanDetail d
INNER JOIN Statements..Fund f
ON d.CUSIP = f.CUSIP
OR
d.Ticker = f.Ticker
OR
d.Ticker = f.ClientFundId
OR
d.CUSIP = f.ClientFundId
-- Do an internal join on the PlanDetail table to get the value of the FundType to derive whether
--fund can only be chosen as part of a portfolio.
LEFT JOIN PlanDetail p
ON d.FundName = p.FundName
AND
d.PortfolioName = p.PortfolioName
WHERE
d.PlanNumber IS NOT NULL
AND
p.PortFundPercent IS NULL
GROUP BY
f.FundId,
d.FundName,
d.FundDefault,
--d.PlanNumber,
--d.Cusip,
-- d.Ticker,
--d.RowNumber,
p.FundType

ORDER BY
Min(d.PlanNumber),
Min(d.RowNumber)


 any help will be appreciated.
Thanks
Karen

View 1 Replies View Related

SELECT DISTINCT W/ORDER BY

Oct 6, 2005

I get the "ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

SELECT DISTINCT
pdm.Account,
pdm.Customer
FROM
dbo.Demands pdm LEFT OUTER JOIN
dbo.Tickets rct ON pdm.Account = rct.Account
WHERE
pdm.Code IN (66, 51)
ORDER BY
pdm.TransactionDate DESC

Is there any way to make the ORDER BY work in this case?

View 1 Replies View Related

SELECT DISTINCT Type ORDER BY

Nov 26, 2007

I am trying to display items from a table that have a type. For each condition there are between 7 and 10 types. I am looping through each type and displaying all items in that type. I am using DISTINCT to pull the types and count them, so I know how many there are and how times to loop. My problem is that DISTINCT is ordering the types alphabetically! I want them in the same order they went into the table. I tried adding ORDER BY primaryID, but got an error that said I also had to select primaryID as well as type, so now I am selecting every item that fits the condition and not just the DISTINCT types! Is there any way to make it order by column_position? I am using SQL 2K.

View 9 Replies View Related

How Can I Use SELECT DISTINCT And Maintain The Original Order

Apr 26, 2007

Say I have a result set with two fields numbers and letters.

1 A3 A1 B2 B


 The result set is ordered by the letters column. How can I select the distinct numbers from the result set but maintain the current order? When I tryselect distinct Number from MyResultSet

it will reorder the new result set by the Number field and return

123

 However, I'd like maintain the Letter order and return

132

View 1 Replies View Related

SELECT DISTINCT MyId Order By MyDate

Nov 3, 2003

I have the following records,
MyDate MyId
2003/10/25 2
2003/10/26 3
2003/10/26 1
2003/10/27 3
2003/10/28 2
2003/10/29 4

I want to get the most earlier date distinct records.
I try to use "SELECT DISTINCT MyID from Table;"
I expect to get Myid: 2,3,1,4
But the computer returns Myid:1,2,3,4

Is there a way to get the records using
"SELECT DISTINCT MyID from Table ORDER BY MyDate;"

Appreciate help......

View 6 Replies View Related

How To Use ORDER BY Clause In An SELECT DISTINCT Sql Query When AS SINGLECOLUMN Is Defined?

Mar 22, 2008

Hi,
I wonder if its possible to perform a ORDER BY clause in an SELECT DISTINCT sql query whereby the AS SINGLECOLUMN is used. At present I am recieving error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. My guess is that I cant perform the Order By clauses because it cant find the columns individually. It is essentail I get this to work somehow...
Can anyone help? Thanks in advance
Gemma

View 10 Replies View Related

How To Select Distinct On One Column

Apr 15, 2015

I have this

SELECT DISTINCT inta, name, PHN#, fROM nydta.adres
WHERE inta <> ' '

I want the distinct for inta because alot of the time phone is blank so those are coming thru i do want all columns, but distinct for inta.

View 1 Replies View Related

SELECT DISTINCT On One Column

Oct 19, 2006

I know this is a popular problem, but i haven't found an answer to my situation.
I have a table (myTable) with three Columns (Column1, Column2, Column3). Column1 entries are unique (with unique identifier), Column2 entries are not unique.
I want to do a SELECT DISTINCT on myTable so that Column2 is unique and i get all the other columns. I thought something like this would work

SELECT DISTINCT Column2 as Col2, newid() as Col1, Column3 as Col4
-- I am getting uniqueidentifier for each row as well

This doesn't seem to work however. I still get all the rows because Col1 is unique.
since i am using aliases, the usual syntax doesn't work either.

any help would be greatly appreciated!

View 2 Replies View Related

Aliased Column In Stored Procedure Not Seen By Datagrid

Sep 17, 2005

i have an aliased column in an sql statement that works fine whendisplaying its output in a datagrid, but when I transfer the sqlstatement into a stored procedure , the datagrid can't see it. I get anerror "{"DataBinder.Eval: 'System.Data.DataRowView' does not contain aproperty with the name myaliasedcolumn." }

View 1 Replies View Related

Transact SQL :: Create Join On Aliased Column

Jun 20, 2015

This is my syntax, but it keeps presenting an error of 'Invalid Column name Last Resort

Select fb.foo, fb.man, cz.choo, 'Hi' As [Last Result]
From foobar As fb
Inner Join chezter As cz
On fb.username = cz.username
and [fb].[Last Result] = [cz].[Last Result]

View 3 Replies View Related

Select With Multiple Column And Distinct

Jun 22, 2008

Hi guys, I'm hoping there's someone out there with more sql knowledge and experience than me. I'll try to explain everything.

I'm trying to create a select statement but i'm not gettting the required results mainly because i think its a very complicated select.

Here is the scenario.

The table has 12 columns

ProductID Colour MD01 MD02 MD03 MD04 MD05 MD06 MD07 MD08 MD09 MD010


The ProductID is naturally the unique key.

There is always a colour value. But there is not always a value in the MD columns. For example one Product may have values in MD01 MD02 MD03 MD04 MD05 whilst another has values in all MD columns.

My problem is thatI am trying to create a results list based upon selecting distinct values from the colour and md columns

In otherwords i can't have more than only one instance of a word appearing in the recordset list

I'm really struggling with this because there are only 6 colours so if i set distinct purely on 6 colours i only get back 6 rows.

When I try to set disctinct across all the MD columns it seems to ignore it and lists results for example in the table

ProductID Colour MD01 MD02 MD03 MD04 MD05 MD06 MD07 MD08 MD09 MD010
1 red car bike
2 blue bike car train

my select lists results as
red
car
bike
blue
bike
car
train

and it is as if it only carries out the distinct command across the row not across all columns for all rows if you see what i mean?

I need to be able to list all data from all rows that have values in the MD columns and colour column but not list the values more than once and not list "empty" (NULL) columns. Does this make sense?

This is the select statement i wrote.

Select DISTINCT md00, md01, md02, md03, md04, md05, md06, md07, md08, md09, md10, colour FROM TEMPLATES WHERE md00 IS NOT NULL or md01 IS NOT NULL or md02 IS NOT NULL or md03 IS NOT NULL or md04 IS NOT NULL or md05 IS NOT NULL or md06 IS NOT NULL or md07 IS NOT NULL or md08 IS NOT NULL or md09 IS NOT NULL or md10 IS NOT NULL

But it returns empty columns and it returns every instance of the same word so in other words the distinct command doesn't seem to be working at all?

I don't know if this is because of my asp code I am trying to list results with the rescordset?

<%
While ((Repeat1__numRows <> 0) AND (NOT template_rs.EOF))
%>

<%=(template_rs.Fields.Item("md01").Value)%>
<%=(template_rs.Fields.Item("md02").Value)%>
<%=(template_rs.Fields.Item("md03").Value)%>
<%=(template_rs.Fields.Item("md04").Value)%>
<%=(template_rs.Fields.Item("md05").Value)%>
<%=(template_rs.Fields.Item("md06").Value)%>
<%=(template_rs.Fields.Item("md07").Value)%>
<%=(template_rs.Fields.Item("md08").Value)%>
<%=(template_rs.Fields.Item("md09").Value)%>
<%=(template_rs.Fields.Item("md10").Value)%>
<%=(template_rs.Fields.Item("colour").Value)%>

<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
template_rs.MoveNext()
Wend
%>


I have one more problem. How can I also in addition to being able to list all distinct results list only results for a specific letter

for example

Select DISTINCT md00, md01, md02, md03, md04, md05, md06, md07, md08, md09, md10, colour FROM TEMPLATES WHERE md00 IS NOT NULL or md01 IS NOT NULL or md02 IS NOT NULL or md03 IS NOT NULL or md04 IS NOT NULL or md05 IS NOT NULL or md06 IS NOT NULL or md07 IS NOT NULL or md08 IS NOT NULL or md09 IS NOT NULL or md10 IS NOT NULL WHERE FIRST LETTER ='A'?


I am so far out of my depth here guys I am hoping that someone who has real knowledge of SQL can help me with this statement. I've been pulling my hair out for days now and getting just more and more frustrated listing the same results :(

-BB

View 20 Replies View Related

Select Distinct Column While Joining Tables

Oct 29, 2014

I am attempting to run the following select statement joining multiple tables but in the end result I would like only Distinct/Unique values to be returned in the invlod.lodnum column.

[select pw.schbat, adrmst.adrnam, adrmst.adrln1, adrmst.adrcty, adrmst.adrstc, adrmst.adrpsz,
invlod.lodnum,
shipment.host_ext_id, shipment_line.ordnum, car_move.car_move_id
from aremst join locmst
on (aremst.arecod = locmst.arecod)
and (aremst.wh_id = locmst.wh_id)

[Code] .....

View 4 Replies View Related

Select Distinct Record Only If Certain Column Not Null

Apr 5, 2007

Been trying to come up with a query to filter-down my sample set intodistinct records. For instance, lets say column1 is a sample set,column2 is the parameter, and column3 is a name and column4 is a type(lets also say there is a fifth column (id) that is an id). What Ineed is one record per type per sample only if type is given, if not,then return that record as well.I've used a subquery to get as close to the desired query is aspossible:select * from table1where id in (select min(id) from table1where column1="A"group by column1, column2)Here's an example of all data for sample "A":1 2 3 4----------A 1 X PA 1 Y PA 1 Z PA 2 WA 3 WA 4 T PA 5 U PA 6 V PA 7 TA 7 UA 7 VI want output :1 2 3-------A 1 X PA 2 WA 3 WA 4 T PA 5 U PA 6 V PA 7 TA 7 UA 7 VExcept the above query will exclude the last two records becausecolumn3 is not 'grouped by'.Basically I need to reduce any 'range' of records per sample (columna) where column4 is not null (ie = 'P'), to only one record andkeeping all others. Thanks in advance:-B

View 6 Replies View Related

Select Distinct Column Data With Other Values From The Table

Dec 16, 2004

I have a table 'wRelated' with the following columns

[related_id] [int]
[channel_id] [int]
[mui] [varchar]
[price_group_id]
[type_id] [int]
[related_mui] [varchar] (100)
[date_started] [smalldatetime]
[date_ended] [smalldatetime]
[date_entered] [datetime]
[deleted] [tinyint],
[rank] [int]
data in column [mui] is repeated as the table has more than one entries for the same [mui],
The requirement is to select the distinct[mui] but value in all the other columns for the same mui should be select in the next row with null for the same [mui]
The recordset expected should be something like this.

[mui],[related_mui],[price_group_id],[date_entered],[date_ended] m123,rm345,'pr','12-10-2003',12-12-2004'
null,rm789,'ar','12-1-2003',26-2-2004'
null,rm999,'xy','14-12-2002',12-2-2004'
m777,rm889,'pr','12-12-2004',12-12-2004'
null,rm785,'yy','1-10-2002',12-12-2004'
m888,rm345,'pr','2-8-2003',12-12-2004'
null,rm345,'tt','30-7-2002',12-12-2004'

I have tried Unions and temporary table inserts.

View 1 Replies View Related

T-SQL (SS2K8) :: How To Select Rows Based On One Distinct Column

Apr 18, 2014

--------------------------------------------------
SalesOrder-ItemName-Price-Category
-------------------------------------------------
01-Camera-100-Electronic
01-Memory-4GB-10-Memory
01-Battery-5-Battery
02-Keyboad-10-Accessories
02-Mouse-5-Accessories
03-CPU-300-Hardware
03-Motherboad-400-Hardware

From above rows i would like to select rows based on one distinct column SalesOrder, i want output like below.

-----------------------------------
SalesOrder-ItemName-Price-Category
-----------------------------------
01-Camera-100-Electronic
02-Keyboad-10-Accessories
03-CPU-300-Hardware

CREATE TABLE Table1 (SalesOrder varchar(10), ItemName VARCHAR(100), Price INT, Category VARCHAR(100))

[Code] ......

View 2 Replies View Related

SELECT INTO A New Table All Columns Based On DISTINCT Value Of One Column

Oct 31, 2014

‘Trying to SELECT INTO a new table all columns of a table based on a DISTINCT value of one column so for example:

SELECT *
INTO new_table
FROM old_name
WHERE old_table.column IS DISTINCT’

View 4 Replies View Related

Can You Use Column Order Value In Select Statement

Nov 6, 2005

Is there a shortcut to spelling out column names when you are doing a select statement?

For instance could you write Select 1, 5, 6 from table where whatever...

I tried this but didn't get any results so if you can I must be using wrong syntax.

Thanks
!

View 2 Replies View Related

How To Select Data From Excel Table By Original Column Order?

Feb 21, 2007

Hello, everyone:

I have an Excel table that has columns like,

Vendoe_Name
Level
Address
Email
Phone

I use the statemen

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C: est.xls;Extended Properties=Excel 8.0')...[Sheet1$]

and got output by column order,

Address
Email
Level
Phone
Vendoe_Name

How to make output by original column order like,

Vendoe_Name
Level
Address
Email
Phone

Any help will appreciated. Thanks

ZYT

View 1 Replies View Related

Using The ORDER BY Clause When The Ordered Column Is Not Needed In The SELECT Statement

May 15, 2008

Greetings,

I have a C# application that calls a stored procedure to query the database (MSSQL 2005). I only have one field/column returned from the query but I need that column ordered.

How do I use the ORDER BY clause without returning the index column which does the sorting? The first example is NOT what I want. I want something that works like the second example which only returns the 'Name' column.


ALTER PROCEDURE [dbo].[MyProcedure]



AS

BEGIN

SELECT DISTINCT A.Name, A.index

FROM
...
...
ORDER BY A.[Index], A.Name ASC

END



ALTER PROCEDURE [dbo].[MyProcedure]



AS

BEGIN

SELECT DISTINCT A.Name
FROM
...
...
ORDER BY A.[Index]

END

Thanks

View 14 Replies View Related

Select DISTINCT On Multiple Columns Is Not Returning Distinct Rows?

Jul 6, 2007

Hi, I have the following script segment which is failing:

CREATE TABLE #LatLong (Latitude DECIMAL, Longitude DECIMAL, PRIMARY KEY (Latitude, Longitude))

INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache



When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'."



Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values.

The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why.

Any ideas?

View 2 Replies View Related

How To Show Distinct Rows Of The Column Of The Dataset And Number Of Distinct Rows Of That Column

Mar 29, 2007

suppose i have aDataset with 11 rows. field1 with 5 rows of aaa, 6 rows of "bbb"

I want's some thing like

field1 rowcount
aaa 5
bbb 6

View 1 Replies View Related

Distinct With Order By

Mar 28, 2007

It gives me this error:
ORDER BY clause (TimeStamp) conflicts with Distinct

The sql statement is:

sql Code:






Original
- sql Code




SELECT DISTINCT division from table order by Stamp DESC






SELECT DISTINCT division FROM TABLE ORDER BY Stamp DESC



I've tried:

sql Code:






Original
- sql Code




SELECT DISTINCT division, Stamp FROM table ORDER BY Stamp DESC






SELECT DISTINCT division, Stamp FROM TABLE ORDER BY Stamp DESC


But this produces duplicate rows, which in this specific case is unacceptable.

Is there any way I can acheive what I want?
Stamp is a TimeStamp if you couldn't figure it out. I just want the last one entered, but all the last ones entered per division. . .

View 9 Replies View Related

Order By Because Of Distinct

Feb 23, 2004

I have a strange situation (I think). Within a view (which explains why I use the top 100% --> to use an order by) I have this query:

select TOP 100 PERCENT *
from tbOfferDetails
where ofd_id = ofd_parent and ofd_fk_off_id = 100

This gives me the following results:
ofd_fk_off_id off_fk_class_id
100 2753
100 2753
100 2071
100 2753

Now I change the query to:

select distinct ofd_fk_off_id, ofd_fk_class_id from
(select TOP 100 PERCENT *
from tbOfferDetails
where ofd_id = ofd_parent and ofd_fk_off_id = 100
order by ofd_sequence ASC ) as tbOffers

This gives me the following results:
ofd_fk_off_id off_fk_class_id
100 2071
100 2753

In the execution plan, it says that a distinct order by is used on off_fk_class_id. My question is: why is this done? I want only a distinct and not an order by. So is there a way to change this (default?) behaviour.

View 14 Replies View Related

Distinct & Dynamic ORDER BY

Apr 29, 2008

Hello,

I have a stored procedure with dynamic ORDER BY. I would like to use the DISTINCT too. Is it somehow possible?
Thank you

Here is the stored procedure:

SELECT identifier_company + cast(identifier_number as nvarchar(3)) as identifier,
CASE WHEN canceled = 'True' THEN 'canceledPO' ELSE '' END AS style,
staff.staff_name,
purchase.purchase_id,
purchase.traveller_name,
nominal_department.department_name,
purchase.canceled,
purchase.travel_date,
convert(nvarchar(20), purchase.date_raised, 103) as dated,
supplier
FROM purchase INNER JOIN purchase_project ON purchase.purchase_id = purchase_project.purchase_id
INNER JOIN staff ON purchase.raised = staff.staff_id
INNER JOIN nominal_department ON purchase.department = nominal_department.nominal_dep_id
WHERE (raised in (SELECT staff_id FROM staff WHERE department like @FromDepartment) or purchase.raised = @raisedBy)
and purchase_project.project_number like '%' + @Query + '%'
ORDER BY
CASE @SortDir
WHEN 'ASC' THEN
CASE @OrderBy
WHEN 'staff_name' THEN cast(staff_name as nvarchar(100))
WHEN 'traveller_name' THEN cast(traveller_name as nvarchar(100))
WHEN 'department_name' THEN cast(department_name as nvarchar(100))
WHEN 'supplier' THEN cast(supplier as nvarchar(100))
WHEN 'canceled' THEN cast(canceled as nvarchar(10))
END
END
ASC,
CASE @SortDir
WHEN 'DESC' THEN
CASE @OrderBy
WHEN 'staff_name' THEN cast(staff_name as nvarchar(100))
WHEN 'traveller_name' THEN cast(traveller_name as nvarchar(100))
WHEN 'department_name' THEN cast(department_name as nvarchar(100))
WHEN 'supplier' THEN cast(supplier as nvarchar(100))
WHEN 'canceled' THEN cast(canceled as nvarchar(10))
END
END
DESC

View 3 Replies View Related

DISTINCT Requires ORDER BY

Jun 28, 2007

OK I am trying the following select statement but I am getting a 'DISTINCT requires ORDER BY to be used' error. I have an ORDER BY in it so I am not sure what I have missed?

SELECT DISTINCT tbl_final_CP.ExtEnum + tbl_final_CP.ExtEnum AS [Full Cost]
FROM tbl_final_CP INNER JOIN
ContractorAreaRelationship ON tbl_final_CP.Area = ContractorAreaRelationship.AreaNo INNER JOIN
tbl_CPCost ON tbl_final_CP.CP = tbl_CPCost.CP
WHERE (DATEPART(yyyy, tbl_final_CP.dCount) = DATEPART(yyyy, GETDATE())) AND (ContractorAreaRelationship.ContractorNumber = 6112) AND
(DATENAME(mm, tbl_final_CP.dCount) = 'Conwy') AND (ContractorAreaRelationship.AreaName = 'Conwy') AND (tbl_final_CP.Video > 0) OR
(DATEPART(yyyy, tbl_final_CP.dCount) = DATEPART(yyyy, GETDATE())) AND (ContractorAreaRelationship.ContractorNumber = 6112) AND
(DATENAME(mm, tbl_final_CP.dCount) = 'March') AND (ContractorAreaRelationship.AreaName = 'Conwy') AND (tbl_final_CP.ExtEnum > 0) OR
(DATEPART(yyyy, tbl_final_CP.dCount) = DATEPART(yyyy, GETDATE())) AND (DATENAME(mm, tbl_final_CP.dCount) = 'March') AND
(ContractorAreaRelationship.AreaName = 'Conwy') AND (tbl_final_CP.Video > 0) AND (ContractorAreaRelationship.AdminID = 6112) OR
(DATEPART(yyyy, tbl_final_CP.dCount) = DATEPART(yyyy, GETDATE())) AND (DATENAME(mm, tbl_final_CP.dCount) = 'March') AND
(ContractorAreaRelationship.AreaName = 'Conwy') AND (tbl_final_CP.ExtEnum > 0) AND (ContractorAreaRelationship.AdminID = 6112)
ORDER BY tbl_final_CP.CP, tbl_final_CP.ExtEnum + tbl_final_CP.ExtEnum

View 2 Replies View Related

SQL Distinct Value And Order By Some Field......

Jan 4, 2006

Dear all,In SQL Server 2000 , how to get distinct records sort by onefield .ExampleSELECT DISTINCT A FROM tblTEST ORBER BY BHere, In TableField 'A' contain more than one same data...Field 'B' contain all are different Data......I want distince in Field 'A' and order by Field 'B'..... how to getit.........regardskrishnan

View 2 Replies View Related

Default Sort Order - Open Table - Select Without Order By

Mar 27, 2008

Hi!

I recently run into a senario when a procedure quiered a table without a order by clause. Luckily it retrived data in the prefered order.

The table returns the data in the same order in SQL Manager "Open Table"

So I started to wonder what deterimins the sort order when there is no order by clause ?

I researched this for a bit but found no straight answers. My table has no PK, but an identiy column.

Peace.

/P

View 5 Replies View Related

Extract Distinct Information And Order The Results

Sep 24, 2007

Hi,

MSSQL 2000 T-SQL

I have a problem in extracting information pertaing to a key value and matching that key value to another transaction but the order is based on another value in the same row.


I've attached a sample of DB data below.

tran_nr ret_ref_no msg_type description
5111 12345 420 reversal
5112 12345 200 auths
5113 15236 200 auths
5114 46587 200 auths
5115 46587 420 reversal

Requirement using the above data is to extract data where the ret_ref_no is the same for more than one row but also check that the msg_type 420 happens before the 200. Is there a way of retrieving the information in this way using the tran_nr coloumn values? The tran_nr values is basically the serial number when the transaction is wrriten away to the DB.

I've managed only to retrive the 1st half of my query whereby the same ret_ref_nr is being used by more then one transaction. Still need to figure out the 2nd part where the msg_type of 420 happens before the 200.


SELECT * FROM SAMPLE
WHERE ret_ref_no in
(
SELECT ret_ref_no FROM SAMPLE
GROUP BY ret_ref_no HAVING COUNT(*) > 1
)

Results of query
5111 12345 420 reversal
5112 12345 200 auths
5114 46587 200 auths
5115 46587 420 reversal

If someone could assist with only retreiving the above results in bold to the query analyser i will really appreciate it.

Regards
Deceptive

View 9 Replies View Related

Default Sort Order When Order By Column Value Are All The Same

Apr 14, 2008

Hi,
We got a problem.
supposing we have a table like this:

CREATE TABLE a (
aId int IDENTITY(1,1) NOT NULL,
aName string2 NOT NULL
)
go
ALTER TABLE a ADD
CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
go


insert into a values ('bank of abcde');
insert into a values ('bank of abcde');
...
... (20 times)

select top 5 * from a order by aName
Result is:
6Bank of abcde
5Bank of abcde
4Bank of abcde
3Bank of abcde
2Bank of abcde

select top 10 * from a order by aName
Result is:
11Bank of abcde
10Bank of abcde
9Bank of abcde
8Bank of abcde
7Bank of abcde
6Bank of abcde
5Bank of abcde
4Bank of abcde
3Bank of abcde
2Bank of abcde

According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. :eek:

Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.

So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?

View 14 Replies View Related

Default Sort Order When The Order By Column Value Are All The Same

Apr 14, 2008

Hi,
We got a problem.
supposing we have a table like this:

CREATE TABLE a (
aId int IDENTITY(1,1) NOT NULL,
aName string2 NOT NULL
)
go
ALTER TABLE a ADD
CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
go

insert into a values ('bank of abcde');
insert into a values ('bank of abcde');
...
... (20 times)

select top 5 * from a order by aName
Result is:
6 Bank of abcde
5 Bank of abcde
4 Bank of abcde
3 Bank of abcde
2 Bank of abcde

select top 10 * from a order by aName
Result is:
11 Bank of abcde
10 Bank of abcde
9 Bank of abcde
8 Bank of abcde
7 Bank of abcde
6 Bank of abcde
5 Bank of abcde
4 Bank of abcde
3 Bank of abcde
2 Bank of abcde

According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users.
Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.
So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?

View 5 Replies View Related

Specify Order For Select Results, Order By: Help!

Nov 17, 2006

Lets say I have a table named [Leadership] and I want to select the field 'leadershipName' from the [Leadership] Table.

My query would look something like this:

Select leadershipName
From Leadership

Now, I would like to order the results of this query... but I don't want to simply order them by ASC or DESC. Instead, I need to order them as follows:

Executive Board Members, Delegates, Grievance Chairs, and Negotiators

My question: Can this be done through MS SQL or do I need to add a field to my [Leadership] table named 'leadershipImportance' or something as an integer to denote the level of importance of the position so that I can order on that value ASC or DESC?

Thanks,

Zoop

View 1 Replies View Related

Transact SQL :: Distinct By One Column By Selecting Multiple Column?

Jul 17, 2015

I have a SQL Query issue you can find in SQL Fiddle

SQL FIDDLE for Demo

My query was like this

For Insert
Insert into Employee values('aa', 'T', 'qqq')
Insert into Employee values('aa' , 'F' , 'qqq')
Insert into Employee values('bb', 'F' , 'eee')
Insert into Employee values('cc' , 'T' , 'rrr')
Insert into Employee values('cc' , 'pp' , 'aaa')
Insert into Employee values('cc' , 'Zz' , 'bab')
Insert into Employee values('cc' , 'ZZ' , 'bac')
For select
select col1,MAX(col2) as Col2,Max(Col3) as Col3
from Employee
group by Col1

I supposed to get last row as 

    cc  Zz  bab

Instead I am getting 

  cc  Zz  rrr 

which is wrong

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved