Showing posts with label problemi. Show all posts
Showing posts with label problemi. Show all posts

Monday, March 19, 2012

Find same records with same ID

Hello,

Having trouble describing my problem

I have the table below, and I am trying to retrieve TileIDs that have the same ModelIDs.

ModelID TileID
HP DL380 G3 120v Dual 15400
HP DL380 G3 120v Dual 15400
HP DL380 G3 120v Dual 15400
HP DL380 G3 120v Dual 15400
HP DL380 G3 120v Dual 15400
HP DL380 G3 120v Dual 15400
Sun SF 280R 120v 15401
Sun SF 280R 120v 15401
Sun SF 280R 120v 15401
Sun SF 280R 120v 15401
Lantronix MSS4 15401

So TileID 15400 would be a keeper, since all ModelIDs are the same.

Any help would be appreciated.

ThanksSELECT * FROM myTable99 WHERE TILEID IN (
SELECT TILEID FROM (
SELECT DISCTINCT MODELID, TILEID
FROM myTable99) AS XXX
GROUP BY TILEID
HAVING COUNT(*) = 1)|||unnecessarily complex, brett

try this:select TileID
from daTable
group by TileID
having COUNT(DISTINCT ModelID) = 1|||Brett,r937

Thanks for the fast responses

I tested both and found that Brett's code is complex for a reason...it worked.|||so did mine, i tested it

:)

Friday, March 9, 2012

find Minimum in range thats not in Table?

Hi Folks,

any help appreciated on this problem:
I've got a Table with a comparable Datatype (inet on postgres).
The values in the table have a minimum and a maximum value.
Now, I've got to find the smallest value betweeen min. and max. that is NOT in the table.

Example:
Min=10, Max=20
Entries: 10, 11, 12, 14, 18
needed value: 13 (larger than Min., smallest value not in Table)
I dont have pure Numbers to deal with, so

I dont want to create an auxiliary table with all the possible values and do a SELECT ... WHERE NOT IN ... statement.

thanks in advance!could you give some examples of what "inet" values are?

kinda curious why you chose the oracle forum to post

http://dbforums.com/f81/ is the postresql forum

and what's wrong with an auxiliary table?

rudy|||Originally posted by r937
could you give some examples of what "inet" values are?

kinda curious why you chose the oracle forum to post

http://dbforums.com/f81/ is the postresql forum

and what's wrong with an auxiliary table?
rudy

Sorry, but my browser says 'SQL and PL/SQL' Forum; I'd say this is a SQL problem - not a postgres.

'inet' is an ip(v4)-address - this datatype is comparable but not incrementable.

I don't think it's necessary to create a table with some hundred continuous values that can be expressed by two borders; perhaps there is no other solution than an auxiliary table but that would be a pity.

thanks anyway, Z|||yes, you're right, there's a separate oracle forum, although this one should definitely have the "PL/SQL" taken off its name

if you want an sql solution and not a postgresql solution, the only ones i'm familiar with are:

-- NOT EXISTS
-- NOT IN
-- EXCEPT
-- OUTER JOIN with test for no match

each of these requires some way of specifying the set of things that aren't there

you don't actually have to have an auxiliary table, though

try joining the table to itself with a left outer join on a.inet = (b.inet - 1)

or something :rolleyes:

rudy|||Originally posted by r937
...
try joining the table to itself with a left outer join on a.inet = (b.inet - 1)

or something :rolleyes:

rudy

darn, you were right from the beginning: As the 'inet'-Type cant be incremented or decremented a pure SQL solution seems even more unapplicable now. I think I have to do some postgres-specific hack... :(

thanks,

zaphod|||yeah, but i didn't know i was right at the time!

meanwhile, i had a look at the postgresql docs, and found this (http://developer.postgresql.org/docs/pgsql/src/test/regress/expected/inet.out) page, which (a) freaks me out, but (b) offers encouragement that there might be custom functions available in postgresql for working with that datatype

rudy