473,699 Members | 2,276 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SELECT DISTINCT Problem

Dear Group

I'm having trouble with the clause below. I would like to select only
records with a distinct TransactionDate but somehow it still lists
duplicates. I need to select the TransactionDate once as smalldatetime and
once as varchar as I'm populating a drop-down with Text/Value pairs. So I
can't just use 'SELECT DISTINCT TransactionDate FROM...'

I'm grateful for any hints.

SELECT DISTINCT (TransactionDat e), CONVERT(varchar (10),Transactio nDate,104)
AS LabelTransactio nDate FROM i2b_keytransact ionlog WHERE ProgClientID =
@ProgClientID ORDER BY TransactionDate ASC

Thanks for your time & efforts!

Martin
Jul 20 '05 #1
5 11686
Dear Group

Here's some more info and why the problem comes up as I just found out.

SELECT DISTINCT TransactionDate FROM i2b_keytransact ionlog

Produces this Result:

2004-04-02 00:12:00
2004-04-02 02:30:00
2004-04-07 02:54:00
2004-04-09 03:13:00

Of course I would get all values as the time is different. In VB all times
show as 00:00:00 so that's why I was confused.
Any chance to do a Distinct on the date only, not the time?

Thanks for your time and efforts!

Martin

"Martin Feuersteiner" <th************ @hotmail.com> wrote in message
news:c5******** **@titan.btinte rnet.com...
Dear Group

I'm having trouble with the clause below. I would like to select only
records with a distinct TransactionDate but somehow it still lists
duplicates. I need to select the TransactionDate once as smalldatetime and
once as varchar as I'm populating a drop-down with Text/Value pairs. So I
can't just use 'SELECT DISTINCT TransactionDate FROM...'

I'm grateful for any hints.

SELECT DISTINCT (TransactionDat e), CONVERT(varchar (10),Transactio nDate,104) AS LabelTransactio nDate FROM i2b_keytransact ionlog WHERE ProgClientID =
@ProgClientID ORDER BY TransactionDate ASC

Thanks for your time & efforts!

Martin

Jul 20 '05 #2
Ok, if I just use
SELECT DISTINCT CONVERT(varchar (10),Transactio nDate,104) FROM
i2b_keytransact ionlog
then I get the desired result but my problem is that I need to include
Transactiondate as it is stored in the database.

Like this:

02.04.2004 2004-04-02 00:12:00
02.04.2004 2004-04-02 02:30:00
07.04.2004 2004-04-07 02:54:00
09.04.2004 2004-04-09 03:13:00

First column is the text of the drop-down item and the second column is the
value of the drop down item.
Any chance I can do a SELECT DISTINCT on this?

"Martin Feuersteiner" <th************ @hotmail.com> wrote in message
news:c5******** **@sparta.btint ernet.com...
Dear Group

Here's some more info and why the problem comes up as I just found out.

SELECT DISTINCT TransactionDate FROM i2b_keytransact ionlog

Produces this Result:

2004-04-02 00:12:00
2004-04-02 02:30:00
2004-04-07 02:54:00
2004-04-09 03:13:00

Of course I would get all values as the time is different. In VB all times
show as 00:00:00 so that's why I was confused.
Any chance to do a Distinct on the date only, not the time?

Thanks for your time and efforts!

Martin

"Martin Feuersteiner" <th************ @hotmail.com> wrote in message
news:c5******** **@titan.btinte rnet.com...
Dear Group

I'm having trouble with the clause below. I would like to select only
records with a distinct TransactionDate but somehow it still lists
duplicates. I need to select the TransactionDate once as smalldatetime and once as varchar as I'm populating a drop-down with Text/Value pairs. So I can't just use 'SELECT DISTINCT TransactionDate FROM...'

I'm grateful for any hints.

SELECT DISTINCT (TransactionDat e),

CONVERT(varchar (10),Transactio nDate,104)
AS LabelTransactio nDate FROM i2b_keytransact ionlog WHERE ProgClientID =
@ProgClientID ORDER BY TransactionDate ASC

Thanks for your time & efforts!

Martin


Jul 20 '05 #3
Ok, if I just use
SELECT DISTINCT CONVERT(varchar (10),Transactio nDate,104) FROM
i2b_keytransact ionlog
then I get the desired result but my problem is that I need to include
Transactiondate as it is stored in the database.

Like this:

02.04.2004 2004-04-02 00:12:00
02.04.2004 2004-04-02 02:30:00
07.04.2004 2004-04-07 02:54:00
09.04.2004 2004-04-09 03:13:00

First column is the text of the drop-down item and the second column is the
value of the drop down item.
Any chance I can do a SELECT DISTINCT on this?
"Martin Feuersteiner" <th************ @hotmail.com> wrote in message
news:c5******** **@titan.btinte rnet.com...
Dear Group

I'm having trouble with the clause below. I would like to select only
records with a distinct TransactionDate but somehow it still lists
duplicates. I need to select the TransactionDate once as smalldatetime and
once as varchar as I'm populating a drop-down with Text/Value pairs. So I
can't just use 'SELECT DISTINCT TransactionDate FROM...'

I'm grateful for any hints.

SELECT DISTINCT (TransactionDat e), CONVERT(varchar (10),Transactio nDate,104) AS LabelTransactio nDate FROM i2b_keytransact ionlog WHERE ProgClientID =
@ProgClientID ORDER BY TransactionDate ASC

Thanks for your time & efforts!

Martin

Jul 20 '05 #4
try this:

SELECT DISTINCT convert(datetim e,cast(Transact ionDate as int)),
CONVERT(varchar (10),Transactio nDate,104)
AS LabelTransactio nDate FROM i2b_keytransact ionlog WHERE ProgClientID =
@ProgClientID ORDER BY TransactionDate ASC

bye,
Helmut
Jul 20 '05 #5
Thanks very much Helmut! It works perfectly!

"hwoess" <in****@iis-edv.at> wrote in message news:<40******* *************** *@newsreader01. highway.telekom .at>...
Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
3665
by: Colman | last post by:
Howdy all! I guess I'm a newbie, because I am stumped (or maybe just too durned tired). Here's what I got... CREATE TABLE `nodecat_map` ( `nodecat_id` mediumint(8) unsigned NOT NULL auto_increment, `nodecat_cat_id` mediumint(8) unsigned NOT NULL default '0', `nodecat_node_id` mediumint(8) unsigned NOT NULL default '0', PRIMARY KEY (`nodecat_id`),
3
6466
by: Tcs | last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't loaded it yet. I'm still using MS Access. And no, I don't believe this is an Access question. (But who knows? I COULD be wrong... :) I've tried the access group...twice...and all I get is "Access doesn't like ".", which I know, or that my query names are too long, as there's a limit to the length of the SQL statement(s). But this works when I don't try to...
9
10884
by: Kelvin | last post by:
Okay so this is baking my noodle. I want to select all the attritbutes/fields from a table but then to excluded any row in which a single attributes data has been duplicated. I.E. Here's my table:- ID Ref Name DATE 1 AAA Joe 1/2 2 BBB Ken 1/2 3 AAA Len 6/3
6
5551
by: John M | last post by:
Hi, The line below is used to feed a combobox. (It is from a database which is used to log pupil behaviour!) The 'incidents' table contains a list of students who have been involved in incidents. Some may appear several times, hence the Distinct. However, thelist generated should still be sorted by Surname. When I add Order by I'm told it conflict with the 'Distinct'. Surely it does not? SELECT DISTINCT ., . & " " & . FROM...
18
3051
by: mathilda | last post by:
My boss has been adamant that SELECT DISTINCT is a faster query than SELECT all other factors being equal. I disagree. We are linking an Access front end to a SQL Server back end and normally are only returning one record. She states that with disctinct the query stops as soon as it finds a matching record. Both of us are relative novices in database theory (obviously). Can someone help settle this?
2
1467
by: ray well | last post by:
i have to extract info from a legacy access database, which i can't alter, or run APPEND or UPDATE quries against. i can only use SELECT statments to extract what i need. the database has multiple entries for the same first and last name, i need to generate a dataset that has no duplicate first and last names. the sql statment itself is quite easy SELECT DISTINCT tblNameAndAddress.LastName, tblNameAndAddress.FirstName
6
13560
by: Bob Stearns | last post by:
I am getting unwanted duplicate rows in my result set, so I added the DISTINCT keyword to my outermost SELECT. My working query then returned the following message: DB2 SQL error: SQLCODE: -214, SQLSTATE: 42822, SQLERRMC: CASE...;ORDER BY;2 Message: An expression in the ORDER BY clause in the following position, or starting with "CASE..." in the "ORDER BY" clause is not valid. Reason code = "2".  More exceptions ... DB2 SQL error:...
5
6893
by: Daniel Wetzler | last post by:
Dear MSSQL experts, I use MSSQL 2000 and encountered a strange problem wqhile I tried to use a select into statement . If I perform the command command below I get only one dataset which has the described properties. If I use the same statement in a select into statement (see the second select) I get several datasets with the described properties like I didn't use distinct
2
3075
bergy
by: bergy | last post by:
Hello MS SQL experts, I'm trying to reorganize some data for a friend and I'm running into this problem. Currently he has some duplicate rows that I need to get rid of - only one of the columns has duplicates and then there is a unique primary key. The table looks like this: id color --- ------ 3 red 4 red 5 blue 6 green
0
8703
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
8623
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9185
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8935
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8893
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5879
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4389
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4636
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2359
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.