Hi,
I'm trying to grab records with a priority over those marked as yes (-1) in
a certain field.
Trying "select id, min(bit) from tab group by id" does not work, as the min
operator doesn't work on bits.
Is there an alternative to my query?
Many thanks,
Chris 9 4738
min(cast(deleted as int))
"Not Me" <No****@faker.fake.fa.ke> wrote in message
news:bu**********@ucsnew1.ncl.ac.uk... Hi,
I'm trying to grab records with a priority over those marked as yes (-1)
in a certain field.
Trying "select id, min(bit) from tab group by id" does not work, as the
min operator doesn't work on bits.
Is there an alternative to my query?
Many thanks, Chris
"mountain man" <hobbit@southern_seaweed.com.op> wrote in message
news:op******************@news-server.bigpond.net.au... "Not Me" <No****@faker.fake.fa.ke> wrote in message news:bu**********@ucsnew1.ncl.ac.uk...
Trying "select id, min(bit) from tab group by id" does not work, as the min operator doesn't work on bits.
Is there an alternative to my query?
min(cast(deleted as int))
Thanks for that, I do though get an error when trying it, I guess it's
because I'm using an mdb file and linked tables to the sql server... any
other ideas? Could create a quick function I guess...
Cheers,
Chris
"Not Me" <No****@faker.fake.fa.ke> wrote in message news:<bu**********@ucsnew1.ncl.ac.uk>... "mountain man" <hobbit@southern_seaweed.com.op> wrote in message news:op******************@news-server.bigpond.net.au... "Not Me" <No****@faker.fake.fa.ke> wrote in message news:bu**********@ucsnew1.ncl.ac.uk...
Trying "select id, min(bit) from tab group by id" does not work, as the min operator doesn't work on bits.
Is there an alternative to my query?
min(cast(deleted as int))
Thanks for that, I do though get an error when trying it, I guess it's because I'm using an mdb file and linked tables to the sql server... any other ideas? Could create a quick function I guess...
Cheers, Chris
Your question isn't really clear - a bit column can only hold 0,1 or
NULL. Perhaps the -1 is coming from Access, not from MSSQL? If it is
an MSSQL query, then please consider posting the CREATE TABLE
statement for your table, as well as the exact query that you're
using, and the output you expect (sample data would also be useful).
Simon
How a bit could be (-1) ?
"Not Me" <No****@faker.fake.fa.ke> wrote in message
news:bu**********@ucsnew1.ncl.ac.uk... Hi,
I'm trying to grab records with a priority over those marked as yes (-1)
in a certain field.
Trying "select id, min(bit) from tab group by id" does not work, as the
min operator doesn't work on bits.
Is there an alternative to my query?
Many thanks, Chris
"Not Me" <No****@faker.fake.fa.ke> wrote in message
news:bu**********@ucsnew1.ncl.ac.uk... "mountain man" <hobbit@southern_seaweed.com.op> wrote in message news:op******************@news-server.bigpond.net.au... "Not Me" <No****@faker.fake.fa.ke> wrote in message news:bu**********@ucsnew1.ncl.ac.uk...
Trying "select id, min(bit) from tab group by id" does not work, as
the min operator doesn't work on bits.
Is there an alternative to my query?
min(cast(deleted as int))
Thanks for that, I do though get an error when trying it, I guess it's because I'm using an mdb file and linked tables to the sql server... any other ideas? Could create a quick function I guess...
How about ..... min(cast(bit as varchar(1))) ?
Pete Brown
Falls Creek
Oz
When you move a database from MS Access to SQL-Server, then do not
translate MS-Access Boolean columns into SQL-Server Bit columns, but use
Tinyint or Char(1) columns instead (and add appropriate CHECK
constraints to limit the column to (0,1) or ('Y','N')).
HTH,
Gert-Jan
Not Me wrote: Hi,
I'm trying to grab records with a priority over those marked as yes (-1) in a certain field.
Trying "select id, min(bit) from tab group by id" does not work, as the min operator doesn't work on bits.
Is there an alternative to my query?
Many thanks, Chris
"Igor Raytsin" <n&*@cyberus.ca> wrote in message
news:40********@news.cybersurf.net... "Not Me" <No****@faker.fake.fa.ke> wrote in message news:bu**********@ucsnew1.ncl.ac.uk... I'm trying to grab records with a priority over those marked as yes (-1) in a certain field.
Trying "select id, min(bit) from tab group by id" does not work, as the min operator doesn't work on bits.
Is there an alternative to my query? How a bit could be (-1) ?
Ask Bill :o)
Chris
"Simon Hayes" <sq*@hayes.ch> wrote in message
news:60**************************@posting.google.c om... "Not Me" <No****@faker.fake.fa.ke> wrote in message
news:<bu**********@ucsnew1.ncl.ac.uk>... "mountain man" <hobbit@southern_seaweed.com.op> wrote in message news:op******************@news-server.bigpond.net.au... "Not Me" <No****@faker.fake.fa.ke> wrote in message news:bu**********@ucsnew1.ncl.ac.uk...
> Trying "select id, min(bit) from tab group by id" does not work, as
the min > operator doesn't work on bits. > > Is there an alternative to my query?
min(cast(deleted as int))
Thanks for that, I do though get an error when trying it, I guess it's because I'm using an mdb file and linked tables to the sql server... any other ideas? Could create a quick function I guess...
Your question isn't really clear - a bit column can only hold 0,1 or NULL. Perhaps the -1 is coming from Access, not from MSSQL? If it is an MSSQL query, then please consider posting the CREATE TABLE statement for your table, as well as the exact query that you're using, and the output you expect (sample data would also be useful).
Thanks for your help, yes the -1 just seems to be how access likes to
display the info.
The full problem, is that I have a table of, for example careers that people
have. In the table certain people (reference numbers) may have a current
job, and a number of non-current jobs. They may have no current job at all
but some past ones.
So, a table could show
id current job
#1 yes databases
#1 no graphics
#2 no statistics
#2 no games
and I would want to return one record for each id#, with a preference of a
current job (if no current job, any non-current job will do)
So far I've only managed to do a "select all current jobs union select all
non-current jobs that don't appear in the current jobs list" The problem
here is that it becomes very very slow when performing the "jobs that don't
appear in the current jobs list" (done by where x not in (select x from y)).
So my effort was to somehow group up the reference numbers, and display the
min(current) job, which would pick the current job as a preference. But the
problem here is I can't add min(job) to the list can I? because that will
not necessary return the correct job associated with the value of
min(current)..
Hope you understand the problem!!
Any help is greatly appreciated.
Cheers,
Chris
Not Me (No****@faker.fake.fa.ke) writes: So, a table could show
id current job #1 yes databases #1 no graphics #2 no statistics #2 no games
and I would want to return one record for each id#, with a preference of a current job (if no current job, any non-current job will do)
So far I've only managed to do a "select all current jobs union select all non-current jobs that don't appear in the current jobs list" The problem here is that it becomes very very slow when performing the "jobs that don't appear in the current jobs list" (done by where x not in (select x from y)).
Here is one way that you may want to try:
DECLARE @temp TABLE (ident int IDENTITY,
id int NOT NULL,
current bit NOT NULL,
job varchar(29) NOT NULL)
INSERT @temp(id, current, job)
SELECT id, current, job
FROM source_table
ORDER BY id, current DESC
SELECT t.id, c.current, t.job
FROM @temp t
JOIN (SELECT id, minident = MIN(ident)
FROM @temp
GROUP BY id) m ON t.ident = m.minident
ORDER BY t.id
By inserting the data into a table variable with an identity column,
the rows are numbered, and the first identity value for each id is the
row you want.
I should add that this trick is not foolproof. You are not really
guaranteed that the identity values actually reflects the ORDER BY
clause, but it works most of the time. Particularly, if there is
no parallelism. Here I am relying on that INSERT into a table variable
never uses parallelism.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: William C. White |
last post by:
Does anyone know of a way to use PHP /w Authorize.net AIM without using
cURL? Our website is hosted on a shared drive and the webhost company
doesn't installed additional software (such as cURL)...
|
by: Albert Ahtenberg |
last post by:
Hello,
I don't know if it is only me but I was sure that header("Location:url")
redirects the browser instantly to URL, or at least stops the execution of
the code. But appearantely it continues...
|
by: James |
last post by:
Hi,
I have a form with 2 fields.
'A'
'B'
The user completes one of the fields and the form is submitted.
On the results page I want to run a query, but this will change
subject to which...
|
by: Ollivier Robert |
last post by:
Hello,
I'm trying to link PHP with Oracle 9.2.0/OCI8 with gcc 3.2.3 on a Solaris9
system. The link succeeds but everytime I try to run php, I get a SEGV from
inside the libcnltsh.so library.
...
|
by: Richard Galli |
last post by:
I want viewers to compare state laws on a single subject.
Imagine a three-column table with a drop-down box on the top. A viewer
selects a state from the list, and that state's text fills the...
|
by: Albert Ahtenberg |
last post by:
Hello,
I have two questions.
1. When the user presses the back button and returns to a form he filled
the form is reseted. How do I leave there the values he inserted?
2. When the...
|
by: inderjit S Gabrie |
last post by:
Hi all
Here is the scenerio ...is it possibly to do this...
i am getting valid course dates output on to a web which i have designed
....all is okay so far , look at the following web url
...
|
by: Jack |
last post by:
Hi All,
What is the PHP equivilent of Oracle bind variables in a SQL statement, e.g.
select x from y where z=:parameter
Which in asp/jsp would be followed by some statements to bind a value...
|
by: Sandwick |
last post by:
I am trying to change the size of a drawing so they are all 3x3.
the script below is what i was trying to use to cut it in half ... I
get errors.
I can display the normal picture but not the...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |